package com.sailfish.springbootdemo.service.db7.Impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.sailfish.springbootdemo.configs.UserHeaderHolder;
import com.sailfish.springbootdemo.dao.db4.UserDao;
import com.sailfish.springbootdemo.dao.db5.H3cToolManagementDao;
import com.sailfish.springbootdemo.dao.db7.*;
import com.sailfish.springbootdemo.dao.db8.vVplPackageDao;
import com.sailfish.springbootdemo.dao.db8.vVplPartDataDao;
import com.sailfish.springbootdemo.pojo.Result;
import com.sailfish.springbootdemo.pojo.db4.User;
import com.sailfish.springbootdemo.pojo.db5.ToolManagement;
import com.sailfish.springbootdemo.pojo.db7.*;
import com.sailfish.springbootdemo.pojo.db8.vVplPackage;
import com.sailfish.springbootdemo.pojo.db8.vVplPartData;
import com.sailfish.springbootdemo.service.db2.impl.S3UploadFileServiceImpl;
import com.sailfish.springbootdemo.service.db7.QueryKnowledgeBaseDataService;
import com.sailfish.springbootdemo.utils.ExcelUtils;
import com.sailfish.springbootdemo.utils.ResultUtil;
import com.sailfish.springbootdemo.utils.CommonUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cursor.Cursor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Slf4j
@Service
public class queryKnowledgeBaseDataServiceImpl implements QueryKnowledgeBaseDataService {
    @Autowired
    private TbTemplateDao tbTemplateDao;
    @Autowired
    private TbTemplateVersionDao tbTemplateVersionDao;
    @Autowired
    private TbTaskVersionDao tbTaskVersionDao;
    @Autowired
    private TbExcipientsDataDao tbExcipientsDataDao;
    @Autowired
    private TbKnowledgeBaseFileDao tbKnowledgeBaseFileDao;
    @Autowired
    private TbKnowledgeBaseDataDao tbKnowledgeBaseDataDao;
    @Autowired
    private TbFlowProcessMainDao tbFlowProcessMainDao;
    @Autowired
    private S3UploadFileServiceImpl s3UploadFileService;
    @Autowired
    private TbKnowledgeBasePushLogicDao tbKnowledgeBasePushLogicDao;
    @Autowired
    private TbFlowProcessRecordsDao tbFlowProcessRecordsDao;
    @Autowired
    private UserDao userDao;
    @Autowired
    private vVplPackageDao vplPackageDao;
    @Autowired
    private vVplPartDataDao vplPartDataDao;
    @Autowired
    private TbTaskTemplateDataDao tbTaskTemplateDataDao;
    @Autowired
    private TbBoardNodeToKnowledgeNodeDao tbBoardNodeToKnowledgeNodeDao;
    @Autowired
    private H3cToolManagementDao toolManagementDao;
    @Value("${personal.config.env}")
    private String env;
    @Value("${personal.config.amazon-s3.file-bucket}")
    private String fileBucket;
    @Value("${personal.config.amazon-s3.url}")
    private String url;
    @Value("${personal.config.file.file-save-url}")
    String rootPath;
    private static final Map<Integer, String> ruleMap = new HashMap<>();
    static {
        ruleMap.put(1, "包含");
        ruleMap.put(2, "不包含");
        ruleMap.put(3, "超过");
        ruleMap.put(4, "不超过");
        ruleMap.put(5, "等于");
        ruleMap.put(6, "不等于");
        ruleMap.put(7, "小于");
        ruleMap.put(8, "不小于");
    }
    private static final int BATCH_SIZE = 1000;
    @Override
    public Result queryDesignRuleDetails(String baseKey){
        try{
            String rule = null;
            List<TbKnowledgeBasePushLogic> logics = null;
            String ruleDetail = "";

            logics = tbKnowledgeBasePushLogicDao.getByBaseKey(baseKey);
            // ? order by sort不起作用，使用map自带的排序功能
            Map<Integer, String> ruleDetailMap = new HashMap<>();

            for(TbKnowledgeBasePushLogic l : logics)
            {
                rule = ". " + l.getBoardParamTypeName() + " "
                        + l.getBoardParamName() + " "
                        + ruleMap.get(l.getLogicRule()) + " "
                        + l.getLogicValue() + "\n";
                ruleDetailMap.put(l.getSort() + 1, rule);
            }
            for(Map.Entry<Integer, String> ruleEntry : ruleDetailMap.entrySet())
            {
                ruleDetail += ruleEntry.getKey() + ruleEntry.getValue();
            }
            JSONObject ruleObject = new JSONObject();
            ruleObject.put("knowledgeBaseLogic", logics);
            ruleObject.put("ruleDetail", ruleDetail);
            return ResultUtil.success(ruleObject);
        }catch (Exception e){
            log.error("error :", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    public Result queryRealValueByKey(String curFlowNo, String templateType, JSONArray result)
    {
        try{
//            List<TbTemplateVersion> vss = tbTemplateVersionDao.getNewestTemplateVersion(templateType);
//            if(vss.isEmpty())
//            {
//                log.error("cannot find newest template");
//                return ResultUtil.error(500, "未能找到最新版本模板", "cannot find newest template", null);
//            }
//            // 找出最新的模板版本
//            TbTemplateVersion latestVersion = vss.get(0);
//            String version = latestVersion.getVersion();
//
//
//            // 当前最新模板的所有param_key表
//            List<TbTemplate> paramKeysOfTemplate =
//                    tbTemplateDao.getTbTemplateByVersion(latestVersion.getTemplate_type(), version);
//
//            // 最后，把value从key转换成实际的值
//            String curVersion = tbKnowledgeBaseDataDao.getVersionByFlowNo(curFlowNo);
//            List<String> parents = tbTemplateDao.getNotValueByParamKeys(templateType,
//                    curVersion,
//                    paramKeysOfTemplate.stream().
//                            map(TbTemplate::getParam_key).
//                            collect(Collectors.toList()));
//            transParamKeyToRealValue(curVersion, templateType, result, parents);
            return null;
        }catch (Exception e){
            log.error("error:", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    public Result queryToolManagement(ToolManagement entity, Integer page, Integer pageSize)
    {
        try{
            List<ToolManagement> rows = new ArrayList<>();
            Integer count = toolManagementDao.getCountBylimit(entity);
            if(Objects.isNull(count) || Objects.equals(0, count)){
                return ResultUtil.tableSuccess(page, pageSize, 0, new JSONArray());
            }
            Map<String, Object> result = new HashMap<>();
            int begin = 0;

            if(!Objects.isNull(page) && !Objects.isNull(pageSize))
            {
                begin = (page - 1) * pageSize;
            }
            if(Objects.isNull(pageSize))
            {
                pageSize = 999999;
            }
            return ResultUtil.tableSuccess(page, pageSize, count, toolManagementDao.getByEntity(entity, begin, pageSize));
        }catch(Exception e){
            log.error("error:", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    public Result downloadBomTemplateExcel()
    {
        try{
            String excelName = "BOM模板";
            // 创建工作簿对象
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            XSSFCellStyle boldStyle = ExcelUtils.getBoldStyle(workbook);//字体加粗
            XSSFCellStyle normalStyle = ExcelUtils.getNormalStyle(workbook);
            final List<String> columnNames = Arrays.asList(new String[]{"PART编码", "位号", "用量","BOM生效日期","项目描述", "Part版本"});
            Row columnNameRow = sheet.createRow(0);
            int colCount = 0;
            for(String c : columnNames)
            {
                Cell columnCell = columnNameRow.createCell(colCount++);
                columnCell.setCellStyle(boldStyle);
                columnCell.setCellValue(c);
            }
            Row dataRow = sheet.createRow(1);
            for(int i = 0; i < colCount; i++)
            {
                Cell dataCell = dataRow.createCell(i);
                dataCell.setCellStyle(normalStyle);
                dataCell.setCellValue("");
            }
            // 导出Excel文件
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String format = sdf.format(new Date());
            File folder = new File(rootPath + "knowledgeBase\\" + format);
            if (!folder.isDirectory()) {
                folder.mkdirs();
            }
            String filePath = rootPath + "knowledgeBase\\" + format + "\\" + excelName + ".xlsx";
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
            return ResultUtil.success(filePath);
        }catch (Exception e){
            log.error("error :", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    public Result outPutExcelOfDeviceData(JSONArray rows) {
        try {
            String excelName = "器件工艺知识库表";
            // Create workbook and sheet
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            XSSFCellStyle boldStyle = ExcelUtils.getBoldStyle(workbook);
            XSSFCellStyle normalStyle = ExcelUtils.getNormalStyle(workbook);
            XSSFCellStyle dateStyle = ExcelUtils.getDateStyle(workbook);

            // Retrieve template versions
            List<TbTemplateVersion> versions = tbTemplateVersionDao.getNewestTemplateVersion("deviceType");
            if (versions.isEmpty()) {
                return ResultUtil.error(500, "当前还未发布工艺知识参数模板", "Device knowledge has not published params yet", null);
            }
            TbTemplateVersion latestVersion = versions.get(0);

            // Fetch template data
            List<TbTemplate> baseTemplates = tbTemplateDao.getTbTemplateKeyByParentKey("deviceType", latestVersion.getVersion(), "deviceType");
            List<TbTemplate> appTemplates = tbTemplateDao.getTbTemplateKeyByParentKey("deviceType", latestVersion.getVersion(), "application");
            List<TbTemplate> descriptionTemplates = tbTemplateDao.getTbTemplateKeyByParentKey("deviceType", latestVersion.getVersion(), "description");

            // Define column headers
            List<String> logInfos = Arrays.asList("维护人员", "更新时间", "创建人员", "创建时间");
            List<String> logKeys = Arrays.asList("function_user", "update_time", "create_user", "create_time");
            List<String> vplParams = Arrays.asList(
                    "封装名", "装联方式", "引脚数量", "是否有极性", "封装外形长mil", "封装外形宽mil",
                    "封装焊盘最小间距mil", "封装原点相对于矩形中心X坐标", "封装原点相对于矩形中心Y坐标",
                    "钢网和焊盘大小是否不一致", "钢网是否分网格设计", "引脚焊盘是否连体设计",
                    "阻焊是否特殊设计", "阻焊是否整体开窗", "插件孔盘最小air gap是否小于32mil",
                    "器件最大高度VPL(mm)", "应力属性", "引脚长度max(mm)", "引脚长度min(mm)",
                    "钢网厚度max(mm)", "钢网厚度min(mm)", "推荐钢网厚度(mm)", "最小压接孔成孔孔径(mm)",
                    "引脚最小pitch(mm)", "插件电源", "插件铝电解电容", "是否需要点胶", "板边小保持力压接连接器"
            );

            // Create header row
            Row headerRow = sheet.createRow(0);
            int colIndex = 0;
            for (TbTemplate tp : baseTemplates) {
                Cell cell = headerRow.createCell(colIndex++);
                cell.setCellStyle(boldStyle);
                cell.setCellValue(tp.getParam_name());
            }
            for (TbTemplate tp : appTemplates) {
                Cell cell = headerRow.createCell(colIndex++);
                cell.setCellStyle(boldStyle);
                cell.setCellValue(tp.getParam_name());
            }
            for (TbTemplate tp : descriptionTemplates) {
                if (!Objects.equals(3, tp.getParam_type()) && !Objects.equals(4, tp.getParam_type())) {
                    Cell cell = headerRow.createCell(colIndex++);
                    cell.setCellStyle(boldStyle);
                    cell.setCellValue(tp.getParam_name());
                }
            }
            for (String logInfo : logInfos) {
                Cell cell = headerRow.createCell(colIndex++);
                cell.setCellStyle(boldStyle);
                cell.setCellValue(logInfo);
            }
            for (String vplParam : vplParams) {
                Cell cell = headerRow.createCell(colIndex++);
                cell.setCellStyle(boldStyle);
                cell.setCellValue(vplParam);
            }

            // Batch fetch task template data
            List<String> flowNos = rows.stream()
                    .map(obj -> ((JSONObject) obj).getString("flow_no"))
                    .filter(Objects::nonNull)
                    .collect(Collectors.toList());
            List<TbTaskTemplateData> taskTemplateDataList = tbTaskTemplateDataDao.getTaskTemplateDataByFlowNos(flowNos);
            Map<String, Map<String, TbTaskTemplateData>> taskDataMap = taskTemplateDataList.stream()
                    .collect(Collectors.groupingBy(
                            TbTaskTemplateData::getFlow_no,
                            Collectors.toMap(TbTaskTemplateData::getParam_name, Function.identity(), (existing, replacement) -> existing)
                    ));

            // Batch fetch VPL package and part data
            List<String> packageNames = taskTemplateDataList.stream()
                    .filter(data -> "器件封装".equals(data.getParam_name()) && data.getParam_value() != null)
                    .map(TbTaskTemplateData::getParam_value)
                    .distinct()
                    .collect(Collectors.toList());
            List<String> componentCodes = taskTemplateDataList.stream()
                    .filter(data -> "器件编码".equals(data.getParam_name()) && data.getParam_value() != null)
                    .map(TbTaskTemplateData::getParam_value)
                    .distinct()
                    .collect(Collectors.toList());

            // Process packageNames in batches
            List<vVplPackage> allVplPackages = new ArrayList<>();
            for (int i = 0; i < packageNames.size(); i += BATCH_SIZE) {
                // 避免Java 模块系统（Module System）权限问题 反射异常
                List<String> batch = new ArrayList<>(packageNames.subList(i, Math.min(i + BATCH_SIZE, packageNames.size())));
                allVplPackages.addAll(vplPackageDao.getvVplPackageByNames(batch));
            }
            Map<String, vVplPackage> packageMap = allVplPackages.stream()
                    .collect(Collectors.toMap(vVplPackage::getPkg_name, Function.identity(), (existing, replacement) -> existing));

            // Process componentCodes in batches
            List<vVplPartData> allVplPartData = new ArrayList<>();
            for (int i = 0; i < componentCodes.size(); i += BATCH_SIZE) {
                // 避免Java 模块系统（Module System）权限问题 反射异常
                List<String> batch = new ArrayList<>(componentCodes.subList(i, Math.min(i + BATCH_SIZE, componentCodes.size())));
                allVplPartData.addAll(vplPartDataDao.getvVplPartDataByCodes(batch));
            }
            Map<String, List<vVplPartData>> partDataMap = allVplPartData.stream()
                    .collect(Collectors.groupingBy(vVplPartData::getCode));


            // Write data rows
            int rowIndex = 1;
            for (Object o : rows) {
                JSONObject jsonObject = (JSONObject) o;
                Row dataRow = sheet.createRow(rowIndex++);
                colIndex = 0;

                // Write base template data
                for (TbTemplate tp : baseTemplates) {
                    Cell cell = dataRow.createCell(colIndex++);
                    cell.setCellStyle(normalStyle);
                    cell.setCellValue(jsonObject.getString(tp.getParam_key()) != null ? jsonObject.getString(tp.getParam_key()) : "");
                }

                // Write app template data
                for (TbTemplate tp : appTemplates) {
                    Cell cell = dataRow.createCell(colIndex++);
                    cell.setCellStyle(normalStyle);
                    cell.setCellValue(jsonObject.getString(tp.getParam_key()) != null ? jsonObject.getString(tp.getParam_key()) : "");
                }

                // Write description template data
                for (TbTemplate tp : descriptionTemplates) {
                    if (!Objects.equals(3, tp.getParam_type()) && !Objects.equals(4, tp.getParam_type())) {
                        Cell cell = dataRow.createCell(colIndex++);
                        cell.setCellStyle(normalStyle);
                        cell.setCellValue(jsonObject.getString(tp.getParam_key()) != null ? jsonObject.getString(tp.getParam_key()) : "");
                    }
                }

                // Write log info data
                for (int i = 0; i < logInfos.size(); i++) {
                    Cell cell = dataRow.createCell(colIndex++);
                    String key = logKeys.get(i);
                    String value = jsonObject.getString(key);
                    if (logInfos.get(i).equals("更新时间") || logInfos.get(i).equals("创建时间")) {
                        cell.setCellStyle(dateStyle);
                        cell.setCellValue(value != null ? value : "");
                    } else {
                        cell.setCellStyle(normalStyle);
                        Object tpObj = jsonObject.getObject(key, Object.class);
                        cell.setCellValue(tpObj instanceof User ?
                                ((User) tpObj).getDisplayName() + "/" +  ((User) tpObj).getDomainAccount()
                                : (value != null ? value : ""));
                    }
                }

                // Write VPL parameters
                String flowNo = jsonObject.getString("flow_no");
                Map<String, TbTaskTemplateData> flowData = taskDataMap.getOrDefault(flowNo, new HashMap<>());
                TbTaskTemplateData packageNameObj = flowData.get("器件封装");
                TbTaskTemplateData componentCodeObj = flowData.get("器件编码");

                String packageName = packageNameObj != null ? packageNameObj.getParam_value() : "";
                String componentCode = componentCodeObj != null ? componentCodeObj.getParam_value() : "";
                vVplPackage pkgData = packageMap.get(packageName);
                List<vVplPartData> partDataList = partDataMap.getOrDefault(componentCode, new ArrayList<>());

                if (pkgData != null) {
                    String[] pkgValues = {
                            pkgData.getPkg_name(), pkgData.getAssembly_type(), String.valueOf(pkgData.getFoot_num()), "",
                            pkgData.getBody_length(), pkgData.getBody_width(), pkgData.getPin_min_pitch(),
                            pkgData.getPitch_o2c_x(), pkgData.getPitch_o2c_y(), pkgData.getIs_size_not_fit(),
                            pkgData.getIs_grid_design(), pkgData.getIs_twins_design(), pkgData.getIs_special_design(),
                            pkgData.getIs_window_open(), pkgData.getIs_small_air_gap()
                    };
                    for (String value : pkgValues) {
                        Cell cell = dataRow.createCell(colIndex++);
                        cell.setCellStyle(normalStyle);
                        cell.setCellValue(value != null ? value : "");
                    }
                } else {
                    for (int i = 0; i < 15; i++) {
                        Cell cell = dataRow.createCell(colIndex++);
                        cell.setCellStyle(normalStyle);
                        cell.setCellValue("");
                    }
                }

                if (!partDataList.isEmpty()) {
                    vVplPartData partData = partDataList.get(0);
                    String maxComponentHeight = partDataList.stream()
                            .filter(obj -> obj.getComponent_high_max() != null && !obj.getComponent_high_max().trim().isEmpty())
                            .map(obj -> obj.getComponent_high_max().replaceAll("(?i)mm$", ""))
                            .flatMap(s -> {
                                try {
                                    return Stream.of(Double.parseDouble(s.trim()));
                                } catch (NumberFormatException e) {
                                    return Stream.empty();
                                }
                            })
                            .max(Double::compare)
                            .map(Object::toString)
                            .orElse("");
                    String[] partValues = {
                            maxComponentHeight, partData.getComp_type(), partData.getPin_high_max(), partData.getPin_high_min(),
                            pkgData != null ? pkgData.getPastemask_thickness_max() : "",
                            pkgData != null ? pkgData.getPastemask_thickness_min() : "",
                            "", // Recommended thickness
                            partData.getPth_diameter_min(),
                            pkgData != null ? pkgData.getPin_min_pitch() : "",
                            partData.getIs_tht_power(), partData.getIs_tht_capcitor(), partData.getIs_glue(),
                            partData.getIs_little_retentionforce()
                    };
                    for (String value : partValues) {
                        Cell cell = dataRow.createCell(colIndex++);
                        cell.setCellStyle(normalStyle);
                        cell.setCellValue(value != null ? value : "");
                    }
                } else {
                    for (int i = 0; i < 13; i++) {
                        Cell cell = dataRow.createCell(colIndex++);
                        cell.setCellStyle(normalStyle);
                        cell.setCellValue("");
                    }
                }
            }

            // Auto-adjust column widths
            for (int i = 0; i < colIndex; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            }

            // Export Excel file
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String format = sdf.format(new Date());
            String filename = excelName + System.currentTimeMillis() + ".xlsx";
            File folder = new File(rootPath + "knowledgeBase\\" + format);
            if (!folder.isDirectory()) {
                folder.mkdirs();
            }
            String filePath = folder.getPath() + "\\" + filename;
            try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
                workbook.write(outputStream);
            }
            workbook.close();
            return ResultUtil.success(filePath);
        } catch (Exception e) {
            log.error("Error generating Excel: ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    public Result syncOutPutExcelOfDeviceData(String templateType,
                                              List<Map<String, String>> keyValueMaps,
                                              int functionFlag,
                                              Integer page,
                                              Integer pageSize,
                                              String searchUser,
                                              String searchFlowType) {
        return null;
    }

    @Override
    public Result getAllDesignPhase(String templateType) {
        try{
            List<String> list = Arrays.asList("器件vpl建库", "维护器件工艺知识库");
            return ResultUtil.success(list);
        }catch(Exception e){
            log.error("error: ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    public Result outPutExcelOfDesignData(JSONArray rows)
    {
        try{
            String excelName = "设计规范知识库表";
            // 创建工作簿对象
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            XSSFCellStyle boldStyle = ExcelUtils.getBoldStyle(workbook);//字体加粗
            XSSFCellStyle normalStyle = ExcelUtils.getNormalStyle(workbook);//字体不加粗
            XSSFCellStyle dateStyle = ExcelUtils.getDateStyle(workbook); //日期类风格
            List<TbTemplateVersion> vs = tbTemplateVersionDao.getNewestTemplateVersion("designType");
            if(vs.isEmpty())
            {
                return ResultUtil.error(500,
                        "当前还未发布工艺知识参数模板",
                        "device knowledge has not published params yet", null);
            }
            TbTemplateVersion latestVerison = vs.get(0);
            List<TbTemplate> baseTemplates
                    = tbTemplateDao.getTbTemplateKeyByParentKey("designType", latestVerison.getVersion(), "designType");
            List<TbTemplate> appTemplates
                    = tbTemplateDao.getTbTemplateKeyByParentKey("designType", latestVerison.getVersion(), "application");
            List<TbTemplate> descriptionTemplates
                    = tbTemplateDao.getTbTemplateKeyByParentKey("designType", latestVerison.getVersion(), "description");
            final List<String> logInfos =
                    Arrays.asList(new String[]{"维护人员", "审核人员", "更新时间","创建人员","创建时间"});

            final List<String> logKeys =
                    Arrays.asList("function_user", "approve_user", "update_time", "create_user", "create_time");

            Row clmNameRow = sheet.createRow(0);
            // 字段名称行固定
            int c = 0;
            for(TbTemplate tp : baseTemplates)
            {
                Cell baseCell = clmNameRow.createCell(c++);
                baseCell.setCellStyle(boldStyle);
                baseCell.setCellValue(tp.getParam_name());
            }
            // 写入 appTemplates 的列名
            for (TbTemplate tp : appTemplates) {
                Cell appCell = clmNameRow.createCell(c++);
                appCell.setCellStyle(boldStyle);
                appCell.setCellValue(tp.getParam_name());
            }
            // 写入 descriptionTemplates 的列名
            for (TbTemplate tp : descriptionTemplates) {
                if(!Objects.equals(3, tp.getParam_type()) && !Objects.equals(4, tp.getParam_type()))
                {
                    Cell descCell = clmNameRow.createCell(c++);
                    descCell.setCellStyle(boldStyle);
                    descCell.setCellValue(tp.getParam_name());
                }
            }
            // 写入 logInfos 的固定列名
            for (String logInfo : logInfos) {
                Cell logCell = clmNameRow.createCell(c++);
                logCell.setCellStyle(boldStyle);
                logCell.setCellValue(logInfo);
            }

            int rowIndex = 1;
            // 写入数据行
            for (Object o : rows) {
                JSONObject jsonObject = (JSONObject) o;
                Row dataRow = sheet.createRow(rowIndex++);
                c = 0; // 每行重置列索引

                // 写入 baseTemplates 的数据
                for (TbTemplate tp : baseTemplates) {
                    Cell baseDataCell = dataRow.createCell(c++);
                    baseDataCell.setCellStyle(normalStyle);
                    String value = jsonObject.getString(tp.getParam_key());
                    baseDataCell.setCellValue(value != null ? value : "");
                }

                // 写入 appTemplates 的数据
                for (TbTemplate tp : appTemplates) {
                    Cell appDataCell = dataRow.createCell(c++);
                    appDataCell.setCellStyle(normalStyle);
                    String value = jsonObject.getString(tp.getParam_key());
                    appDataCell.setCellValue(value != null ? value : "");
                }

                // 写入 descriptionTemplates 的数据
                for (TbTemplate tp : descriptionTemplates) {
                    // 可能是文件类或图片附件
                    Object tpObj = jsonObject.getObject(tp.getParam_key(), Object.class);
                    if(tpObj instanceof List<?>)
                    {
                        log.info("file value, continue");
                    }else{
                        Cell descDataCell = dataRow.createCell(c++);
                        descDataCell.setCellStyle(normalStyle);
                        String value = jsonObject.getString(tp.getParam_key());
                        descDataCell.setCellValue(value != null ? value : "");
                    }
                }

                // 写入 logInfos 的数据
                for (int i = 0; i < logInfos.size(); i++) {
                    Cell logDataCell = dataRow.createCell(c++);
                    String key = logKeys.get(i);
                    String value = jsonObject.getString(key);
                    // 对时间字段（更新时间、创建时间）应用 dateStyle
                    if (logInfos.get(i).equals("更新时间") || logInfos.get(i).equals("创建时间")) {
                        logDataCell.setCellStyle(dateStyle);
                        // 如果值非空，尝试解析为日期（假设值是字符串格式的日期）
                        if (value != null && !value.isEmpty()) {
                            try {
                                // 假设日期格式为 ISO 格式或类似，需根据实际格式调整
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                Date date = sdf.parse(value);
                                logDataCell.setCellValue(value);
                            } catch (ParseException e) {
                                // 如果解析失败，直接写入字符串
                                logDataCell.setCellValue(value);
                            }
                        } else {
                            logDataCell.setCellValue("");
                        }
                    } else {
                        Object tpObj = jsonObject.getObject(key, Object.class);
                        if(tpObj instanceof User)
                        {
                            logDataCell.setCellStyle(normalStyle);
                            logDataCell.setCellValue(((User) tpObj).getUserName());
                        }else{
                            logDataCell.setCellStyle(normalStyle);
                            logDataCell.setCellValue(value != null ? value : "");
                        }
                    }
                }

            }

            // 自动调整列宽
            for (int i = 0; i < c; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            }

            // 导出Excel文件
            String filename = excelName + System.currentTimeMillis();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String format = sdf.format(new Date());
            File folder = new File(rootPath + "knowledgeBase\\" + format);
            if (!folder.isDirectory()) {
                folder.mkdirs();
            }
            String filePath = rootPath + "knowledgeBase\\" + format + "\\" + filename + ".xlsx";
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
            return ResultUtil.success(filePath);
        }catch (Exception e){
            log.error("error :", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    public Result importBomExcel(MultipartFile file){
        try {
            // 解析Excel文件，获取批量数据
            List<LinkedHashMap<String, String>> batchData = ExcelUtils.parseExcelSheet0(file);
            // 检查数据是否为空
            if (Objects.isNull(batchData) || batchData.size() == 0) {
                return ResultUtil.error(500, "表格没有BOM数据", "no BOM data in the chart", null);
            }
            // 检索集，会包含器件partNo以及封装的检索条件
            Map<String, List<Map<String, String>>> deviceSearchObject = new HashMap<>();
            // 器件检索条件表
            List<Map<String, String>> keyValueMaps = new ArrayList<>();

            // 获取器件编码和封装的 key
            String deviceCodeKey = null;
            String devicePackageKey = null;
            List<TbTemplateVersion> latestVs = tbTemplateVersionDao.getNewestTemplateVersion("deviceType");
            if(latestVs != null && latestVs.size() == 1)
            {
                TbTemplateVersion version = latestVs.get(0);
                deviceCodeKey = tbTemplateDao.getTbTemplateKeyByName(version.getTemplate_type(), version.getVersion(), "器件编码");
                devicePackageKey = tbTemplateDao.getTbTemplateKeyByName(version.getTemplate_type(), version.getVersion(), "器件封装");
            }

            for(LinkedHashMap<String, String> dt : batchData)
            {
                // key为器件编码或封装param_key
                Map<String, String> keyToValuePartNo = new HashMap<>();
                keyToValuePartNo.put("key", deviceCodeKey);
                String partCode = dt.get("PART编码");
                if(partCode == null)
                {
                    return ResultUtil.error(500, "导入表格PART编码列缺失", "excel has no column named 'PART编码'", null);
                }
                keyToValuePartNo.put("value", partCode);
                keyValueMaps.add(keyToValuePartNo);
                Map<String, String> keyToValuePkg = new HashMap<>();
                keyToValuePkg.put("key", devicePackageKey);
                keyToValuePkg.put("value", getPartPkgByNo(partCode));
                keyValueMaps.add(keyToValuePkg);
            }
            deviceSearchObject.put("conditions", keyValueMaps);
            return ResultUtil.success(deviceSearchObject);
        }catch (Exception e){
            log.error("error : ", e);
            e.printStackTrace();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    public Result importBomExcelInTool(MultipartFile file){
        try {
            // 解析Excel文件，获取批量数据
            List<LinkedHashMap<String, String>> batchData = ExcelUtils.parseExcelSheet0(file);
            // 检查数据是否为空
            if (Objects.isNull(batchData) || batchData.size() == 0) {
                return ResultUtil.error(500, "表格没有BOM数据", "no BOM data in the chart", null);
            }
            // 检索集，会包含器件partNo以及封装的检索条件
            Map<String, List<Map<String, String>>> deviceSearchObject = new HashMap<>();
            // 器件检索条件表
            List<Map<String, String>> bomParams = new ArrayList<>();

//            // 获取器件编码和封装的 key
//            String deviceCodeKey = null;
//            String devicePackageKey = null;
//            List<TbTemplateVersion> latestVs = tbTemplateVersionDao.getNewestTemplateVersion("deviceType");
//            if(latestVs != null && latestVs.size() == 1)
//            {
//                TbTemplateVersion version = latestVs.get(0);
//                deviceCodeKey = tbTemplateDao.getTbTemplateKeyByName(version.getTemplate_type(), version.getVersion(), "器件编码");
//                devicePackageKey = tbTemplateDao.getTbTemplateKeyByName(version.getTemplate_type(), version.getVersion(), "器件封装");
//            }

            for(LinkedHashMap<String, String> dt : batchData)
            {
                // key为器件编码或封装param_key
                Map<String, String> bomParamMap = new HashMap<>();
                String partCode = dt.get("PART编码");
                String posNumber = dt.get("位号");
                if(partCode == null)
                {
                    return ResultUtil.error(500, "导入表格PART编码列缺失", "excel has no column named 'PART编码'", null);
                }
                if(posNumber == null)
                {
                    return ResultUtil.error(500, "导入表格位号列缺失", "excel has no column named 'PART编码'", null);
                }
                bomParamMap.put("partNo", partCode);
                bomParamMap.put("packageName", getPartPkgByNo(partCode));
                bomParamMap.put("posNo", posNumber);
                bomParams.add(bomParamMap);
            }
            deviceSearchObject.put("bomParams", bomParams);
            return ResultUtil.success(deviceSearchObject);
        }catch (Exception e){
            log.error("error : ", e);
            e.printStackTrace();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result insertOrUpdateKnowledgeBaseLogic(List<TbKnowledgeBasePushLogic> logics, List<String> keyList){
        try {
            // 实际是先删除再插入，永远不会有update
            int affectedRows = 0;
            if(!keyList.isEmpty())
            {
                tbKnowledgeBasePushLogicDao.deleteByBaseKey(keyList);
            }

            List<TbKnowledgeBasePushLogic> allLogicList = new ArrayList<>();
            for(String key : keyList)
            {
                for(TbKnowledgeBasePushLogic l : logics)
                {
                    l.setKnowledge_base_key(key);
                }
                allLogicList.addAll(logics);

            }
            // 批量插入
            if(!allLogicList.isEmpty())
            {
                affectedRows += tbKnowledgeBasePushLogicDao.batchInsert(logics);
            }

            log.info("insert rows:" + affectedRows);

            return ResultUtil.success("操作成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    public Result getMergeKnowledgeBaseLogic(List<String> baseKeyList){
        try{
            if(Objects.isNull(baseKeyList) || baseKeyList.isEmpty())
            {
                return ResultUtil.error(500, "空的knowledge_base_key", "knowledge_base_key list is null", null);
            }
            List<TbKnowledgeBasePushLogic> logics = tbKnowledgeBasePushLogicDao.getByBaseKeyList(baseKeyList);
            LinkedHashSet<TbKnowledgeBasePushLogic> uniqueLogics = new LinkedHashSet<>(logics);
            Map<String, List<TbKnowledgeBasePushLogic>> map = new HashMap<>();
            // 将去重后的结果转回列表以便排序
            List<TbKnowledgeBasePushLogic> sortedList = new ArrayList<>(uniqueLogics);

            // 按照 sort 属性排序
            sortedList.sort(Comparator.comparingInt(TbKnowledgeBasePushLogic::getSort));
            map.put("knowledgeBaseLogic", sortedList);
            return ResultUtil.success(map);
        }catch (Exception e){
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result updateIsDefaultPush(String knowledgeBaseKey, Integer isDefaultPush)
    {
        try {
            if (knowledgeBaseKey == null || knowledgeBaseKey.trim().isEmpty()) {
                return ResultUtil.error(400, "knowledgeBaseKey cannot be null or empty", "Invalid input", null);
            }
            Date now = new Date();
            String userId = UserHeaderHolder.getUserId();
            int affectedRows = tbKnowledgeBaseDataDao.updateIsDefaultPush(knowledgeBaseKey, isDefaultPush, userId, now);

            log.info("updateIsDefaultPush affectedRows is {}", affectedRows);
            return ResultUtil.success("操作成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    public List<TbTemplate> getAllTemplateParams(String templateType){
        try{
            List<TbTemplate> templateParams = new ArrayList<>();
            if(StringUtils.isEmpty(templateType))
            {
                return templateParams;//ResultUtil.error(500, "空的模板类型","empty template", 500);
            }
            List<TbTemplateVersion> vss = tbTemplateVersionDao.getNewestTemplateVersion(templateType);
            if(vss.isEmpty())
            {
                log.error("cannot find newest template");
                return templateParams;
            }
            // 找出最新的模板版本
            TbTemplateVersion latestVersion = vss.get(0);

            if (latestVersion == null) {
                log.error("cannot find newest template");
                return templateParams;
            }

            // 当前最新模板的所有param_key表
            templateParams =
                    tbTemplateDao.getTbTemplateByVersion(latestVersion.getTemplate_type(), latestVersion.getVersion());
            return templateParams;
        }catch (Exception e){
            return null;
        }
    }
    @Override
    public List<TbTemplate> getAllBaseTemplateParamList(List<TbTemplate> tree, String templateType)
    {
        Integer isValue = new Integer(2);
        List<TbTemplate> logTemplateList = new ArrayList<>();
        List<TbTemplate> applicationTemplateList = new ArrayList<>();
        List<TbTemplate> descriptionTemplateList = new ArrayList<>();
        List<TbTemplate> baseTypeTemplateList = new ArrayList<>();
        for (TbTemplate e : tree
        ) {
            switch (e.getParam_key())
            {
                case "logInfo":
                    logTemplateList = e.getChildren().stream()
                            .filter(element -> isValue.equals(element.getIs_value()))
                            .collect(Collectors.toList());
                    break;
                case "application":
                    applicationTemplateList = e.getChildren().stream()
                            .filter(element -> isValue.equals(element.getIs_value()))
                            .collect(Collectors.toList());
                    break;
                case "description":
                    descriptionTemplateList = e.getChildren().stream()
                            .filter(element -> isValue.equals(element.getIs_value()))
                            .collect(Collectors.toList());
                    break;
                default:
                    if (templateType.equals(templateType)) {
                        baseTypeTemplateList = e.getChildren().stream()
                                .filter(element -> isValue.equals(element.getIs_value()))
                                .collect(Collectors.toList());
                    }
                    if (templateType.equals(templateType)) {
                        baseTypeTemplateList = e.getChildren().stream()
                                .filter(element -> isValue.equals(element.getIs_value()))
                                .collect(Collectors.toList());
                    }
                    break;
            }
        }
        for (TbTemplate e : tree
        ) {
            switch (e.getParam_key())
            {
                case "logInfo":
                    logTemplateList.addAll(e.getChildren().stream()
                            .filter(element -> !(isValue.equals(element.getIs_value())))
                            .collect(Collectors.toList()));
                    break;
                case "application":
                    applicationTemplateList.addAll(e.getChildren().stream()
                            .filter(element -> !(isValue.equals(element.getIs_value())))
                            .collect(Collectors.toList()));
                    break;
                case "description":
                    descriptionTemplateList.addAll(e.getChildren().stream()
                            .filter(element -> !(isValue.equals(element.getIs_value())))
                            .collect(Collectors.toList()));
                    break;
                default:
                    if (templateType.equals(templateType)) {
                        baseTypeTemplateList.addAll(e.getChildren().stream()
                                .filter(element -> !(isValue.equals(element.getIs_value())))
                                .collect(Collectors.toList()));
                    }
                    if (templateType.equals(templateType)) {
                        baseTypeTemplateList.addAll(e.getChildren().stream()
                                .filter(element -> !(isValue.equals(element.getIs_value())))
                                .collect(Collectors.toList()));
                    }
                    break;
            }
        }
        List<TbTemplate> data = new ArrayList<>();
        data.addAll(logTemplateList);
        data.addAll(descriptionTemplateList);
        data.addAll(applicationTemplateList);
        data.addAll(baseTypeTemplateList);
        return data;
    }
    @Override
    public List<TbTemplate> getExcipientTemplateParams(Integer status){
        List<TbTemplate> list;
        if(new Integer(0).equals(status))
        {
            list = tbTemplateDao.getTbTemplateByLimit("materialType", null, null);
        }else{
            list = tbTemplateDao.getTbTemplateByLimit("materialType", null, status);
        }
        list = list.stream().sorted(Comparator.comparingInt(TbTemplate::getSort))
                .collect(Collectors.toList());
        return list;
    }
    @Override
    public Result queryDeviceData(List<Map<String, String>> bomParamList, int page, int pageSize, String node)
    {
        return ResultUtil.tableSuccess(page, pageSize, 0, new ArrayList<>());
    }
//    {
//        try {
//            String templateType = "deviceType";
//            List<TbTemplateVersion> vss = tbTemplateVersionDao.getNewestTemplateVersion(templateType);
//            List<TbBoardNodeToKnowledgeNode> boardNodeToKnowledgeNodeList =
//                    tbBoardNodeToKnowledgeNodeDao.getByBoardNodeName(node);
//            if(!boardNodeToKnowledgeNodeList.isEmpty())
//            {
//                node = boardNodeToKnowledgeNodeList.get(0).getKnowledge_node_name();
//            }
//            log.info("queryDeviceData node is " + node);
//            if(vss.isEmpty())
//            {
//                log.error("cannot find newest template");
//                return ResultUtil.error(500, "未能找到最新版本模板", "cannot find newest template", null);
//            }
//            // 找出最新的模板版本
//            TbTemplateVersion latestVersion = vss.get(0);
//            String version = latestVersion.getVersion();
//            if (latestVersion == null) {
//                log.error("cannot find newest template");
//                return ResultUtil.error(500, "未能找到最新版本模板", "cannot find newest template", null);
//            }
//            log.info("latest version is " + version);
//
//            LinkedHashMap<String, List<TbKnowledgeBaseData>> groupedByFlowNo;
//
//
//            // 器件相关的param_key
//            String deviceCodeKey = "";
//            String devicePackageKey = "";
//
//            deviceCodeKey = tbTemplateDao.getTbTemplateKeyByName(templateType, version, "器件编码");
//            devicePackageKey = tbTemplateDao.getTbTemplateKeyByName(templateType, version, "器件封装");
//            // 设计阶段的param_key
//            String designPahseKey = tbTemplateDao.getTbTemplateKeyByName(templateType, version, "设计阶段");
//
//            // 计算起始行索引
//            int begin = Objects.isNull(page) ? 0 : (page - 1) * pageSize;
//            AtomicInteger start = new AtomicInteger();
//            AtomicInteger end = new AtomicInteger();
//            AtomicInteger total = new AtomicInteger(0); // 总数量
//            // 此方法调用需要返回包含bom的器件编码，封装，装联方式等信息，兼容getFilteredByDataBase，
//            // 以keyToMatchedIndexes记录匹配特征(key为匹配到的知识库key，value为partNumberAndNameList的索引)
//            List<Map<String, String>> partNumberAndNameList = new ArrayList<>();
//            int index = 0;
//            for (Map<String, String> param : bomParamList) {
//                Map<String, String> partNumberMap = new HashMap<>();
//                partNumberMap.put("key", deviceCodeKey);
//                partNumberMap.put("value", param.get("partNo"));
//                partNumberMap.put("index", String.valueOf(index));
//                Map<String, String> packageNameMap = new HashMap<>();
//                packageNameMap.put("key", devicePackageKey);
//                packageNameMap.put("value", param.get("packageName"));
//                packageNameMap.put("index", String.valueOf(index));
//                partNumberAndNameList.add(partNumberMap);
//                partNumberAndNameList.add(packageNameMap);
//                index++;
//            }
//
//            Map<String, List<Integer>> keyToMatchedIndexes = new HashMap<>();
//            LinkedHashSet<String> commonKeySet = getFilteredByDataBase(partNumberAndNameList, keyToMatchedIndexes);
//
//
//            // 加载所有归档数据,使用flowno分组
//            total.set(commonKeySet.size());
//            if(commonKeySet.size() == 0)
//            {
//                log.error("commonKeySet is empty");
//                return ResultUtil.success("没有已归档的知识库");
//            }
//            groupedByFlowNo =
//                    tbKnowledgeBaseDataDao.getByBaseKeyList(commonKeySet).
//                            stream().
//                            collect(Collectors.groupingBy(TbKnowledgeBaseData::getFlow_no,
//                                    LinkedHashMap::new,
//                                    Collectors.toList()));
//            getPaginationInfo(begin, pageSize, start, end, commonKeySet.size());
//            if(groupedByFlowNo.size() == 0)
//            {
//                log.error("groupedByFlowNo is empty");
//                return ResultUtil.success("没有已归档的知识库");
//            }
//
//            // 当前最新模板的所有param_key表
//            List<TbTemplate> paramKeysOfTemplate =
//                    tbTemplateDao.getTbTemplateByVersion(latestVersion.getTemplate_type(), version);
//
//            Map<String, List<TbTemplate>> templateMap = paramKeysOfTemplate.stream()
//                    .filter(t -> !StringUtils.isEmpty(t.getParent_param_key()))
//                    .collect(Collectors.groupingBy(TbTemplate::getParent_param_key));
//            // 应用场合
//            List<TbTemplate> applicationTemplateList = templateMap.getOrDefault("application", Collections.emptyList());
//
//            // 基本信息
//            List<TbTemplate> baseTypeTemplateList = templateMap.getOrDefault(templateType, Collections.emptyList());
//
//            // 正文描述
//            List<TbTemplate> descTemplateList = new ArrayList<>();
//
//            descTemplateList = templateMap.getOrDefault("description", Collections.emptyList());
//
//            if (applicationTemplateList == null || baseTypeTemplateList == null) {
//                return ResultUtil.error(500, "模板数据列缺失", "some template column is null", null);
//            }
//            // 获取最新模板所有列名集合
//            List<String> applicationParamKeys = applicationTemplateList.
//                    stream().
//                    map(TbTemplate::getParam_key).
//                    collect(Collectors.toList());
//
//            List<String> baseTypeParamKeys = baseTypeTemplateList.
//                    stream().
//                    map(TbTemplate::getParam_key).
//                    collect(Collectors.toList());
//
//            List<String> descParamKeys = descTemplateList.
//                    stream().
//                    map(TbTemplate::getParam_key).
//                    collect(Collectors.toList());
//            // 遍历每个分组并进行处理
//            JSONArray result = new JSONArray();
//            for (Map.Entry<String, List<TbKnowledgeBaseData>> entry : groupedByFlowNo.entrySet()) {
//                String curFlowNo = entry.getKey();
//
//                // 以当前知识所在的模板版本为标准适配
//                String curVersion = tbKnowledgeBaseDataDao.getVersionByFlowNo(curFlowNo);
//                log.info("cur flow_no is " + curFlowNo);
//
//                String knowledgeBaseKey = entry.getValue().get(0).getKnowledge_base_key();
//
//                int maxRow;
//                // 在这里处理每个分组
//                List<TbKnowledgeBaseData> group = entry.getValue();
//
//                // 日志信息注释掉，从流程信息直接获取日志信息
////                List<TbKnowledgeBaseInfo> logTaskTemplateDataList = group.stream()
////                        .filter(item -> "logInfo".equals(item.getParent_param_key())).collect(Collectors.toList());
//
//                // 基本信息只保留器件编码和器件封装的数据
//                String finalDeviceCodeKey = deviceCodeKey;
//                String finalDevicePackageKey = devicePackageKey;
//                List<TbKnowledgeBaseData> baseTypeTaskDataList = group.stream()
//                        .filter(item -> templateType.equals(item.getParent_param_key())
//                                && (finalDeviceCodeKey.equals(item.getParam_key()) ||
//                                finalDevicePackageKey.equals(item.getParam_key()))).
//                        collect(Collectors.toList());
//                // 应用场合
//                List<TbKnowledgeBaseData> applicationTaskDataList = group.stream()
//                        .filter(item -> "application".equals(item.getParent_param_key()))
//                        .collect(Collectors.toList());
//                // 正文描述
//                List<TbKnowledgeBaseData> descriptionDataList = group.stream()
//                        .filter(item -> "description".equals(item.getParent_param_key())).collect(Collectors.toList());
//                JSONArray appArray = addToTemplateDataArray(applicationParamKeys, applicationTaskDataList, true);
//
//                maxRow = Math.max(appArray.size(), 0);
//                // 添加基本信息数据
//                JSONArray baseTypeArray = addToTemplateDataArray(baseTypeParamKeys, baseTypeTaskDataList, false);
//                // 只会包含器件编码和器件封装的查询
//                //filtByPartNoAndPackageName(baseTypeArray, bomParamList, latestVersion);
//                if ((baseTypeArray.size() == 0)) {
//                    continue;
//                }
//                maxRow = Math.max(baseTypeArray.size(), maxRow);
//
//                JSONArray descriptArray = new JSONArray();
//
//                maxRow = Math.max(descriptArray.size(), maxRow);
//                // 补齐空行使所有array的size都为maxRow
//
//                descriptArray = addToTemplateDataArray(descParamKeys, descriptionDataList, true);
//                addDataRowToMaxRow(appArray, applicationParamKeys, maxRow, true);
//                addDataRowToMaxRow(baseTypeArray, baseTypeParamKeys, maxRow, false);
//                addDataRowToMaxRow(descriptArray, descParamKeys, maxRow, false);
//
//
//                for (int i = 0; i < maxRow; i++) {
//                    JSONObject temp = new JSONObject();
//
//                    temp.put("knowledge_base_key", knowledgeBaseKey);
//
//                    temp.put("flow_no", curFlowNo);
//                    temp.put("flow_type",
//                            tbTaskVersionDao.getTaskVersionByFlowNo(curFlowNo).get(0).getTask_type());
//                    //
//                    temp.putAll(appArray.getJSONObject(i));
//                    //temp.putAll(logArray.getJSONObject(i));
//                    temp.putAll(descriptArray.getJSONObject(i));
//
//                    temp.putAll(baseTypeArray.getJSONObject(i));
//                    //temp.putAll(logArray.getJSONObject(i));
//                    result.add(temp);
//                    List<String> parents = tbTemplateDao.getNotValueByParamKeys(templateType,
//                            curVersion,
//                            paramKeysOfTemplate.stream().
//                                    map(TbTemplate::getParam_key).
//                                    collect(Collectors.toList()));
//                    transParamKeyToRealValue(curVersion, templateType, temp, parents);
//                }
//                log.info("flow_no " + curFlowNo + " matched now result size is " + result.size());
//
//            }
//
//            return ResultUtil.tableSuccess(page, pageSize, total.get(), result);
//        }catch (Exception e){
//            log.error("error : ", e);
//            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
//        }
//    }

    // 辅助方法，用于解析带百分号的字符串到double
    private static double parseToDouble(String value) throws NumberFormatException {
        if (value.endsWith("%")) {
            // 移除百分号并转换为double
            return Double.parseDouble(value.substring(0, value.length() - 1)) / 100.0;
        } else {
            return Double.parseDouble(value);
        }
    }

    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result queryKnowledgeDataByLimit(String templateType,
                                            List<Map<String, String>> keyValueMaps,
                                            int functionFlag,
                                            Integer page,
                                            Integer pageSize,
                                            String searchUser,
                                            String searchFlowType)
    {
        try{
            if(StringUtils.isEmpty(templateType))
            {
                log.error("templateType is null");
                return ResultUtil.error(500, "模板类型不能为空", "templateType cannot be null", null);
            }
            List<TbTemplateVersion> vss = tbTemplateVersionDao.getNewestTemplateVersion(templateType);
            if(vss.isEmpty())
            {
                log.error("cannot find newest template");
                return ResultUtil.error(500, "无有效发布模板", "cannot find newest template", null);
            }
            // 找出最新的模板版本
            TbTemplateVersion latestVersion = vss.get(0);
            String version = latestVersion.getVersion();
            if (latestVersion == null) {
                log.error("cannot find newest template");
                return ResultUtil.error(500, "无有效发布模板", "cannot find newest template", null);
            }
            log.info("latest version is " + version);
            // 根据模板类型和前端的keyValueMaps进一步筛选查询结果
            // 所有key和value都为null相当于没有查询
            // key不为空value为空相当于无效查询，去掉
            boolean hasValidContidionts = true;
            List<Map<String, String>> filteredKeyValueMaps = keyValueMaps.stream()
                    .filter(map -> {
                        String key = map.get("key");
                        String value = map.get("value");
                        return key != null && !key.isEmpty() && value != null && !value.isEmpty();
                    })
                    .collect(Collectors.toList());
            Map<String, List<Map<String, String>>> keyValueMapsByFather = new HashMap<>();

            if(filteredKeyValueMaps.size() == 0 )
            {
                hasValidContidionts = false;
            }

            if(hasValidContidionts)
            {
                // 提取所有 key
                List<String> keys = filteredKeyValueMaps.stream()
                        .map(map -> map.get("key"))
                        .distinct()
                        .collect(Collectors.toList());

                // 批量查询 key 到 parent_param_key 的映射
                List<TbTemplate> templates = tbTemplateDao.getParentParamKeys(templateType, version, keys);
                Map<String, String> keyToParentMap = templates.stream()
                        .collect(Collectors.toMap(
                                TbTemplate::getParam_key,
                                TbTemplate::getParent_param_key,
                                (existing, replacement) -> existing // 处理重复 key
                        ));

                // 按 parent_param_key 分组
                keyValueMapsByFather = filteredKeyValueMaps.stream()
                        .collect(Collectors.groupingBy(
                                map -> {
                                    String key = map.get("key");
                                    return keyToParentMap.getOrDefault(key, templateType);
                                },
                                HashMap::new,
                                Collectors.toList()
                        ));

            }
            // 确保所有预期键存在
            keyValueMapsByFather.putIfAbsent("application", new ArrayList<>());
            keyValueMapsByFather.putIfAbsent("description", new ArrayList<>());
            keyValueMapsByFather.putIfAbsent(templateType, new ArrayList<>());

            // 分页计算
            // 计算起始行索引
            int begin = Objects.isNull(page) ? 0 : (page - 1) * pageSize;
            AtomicInteger start = new AtomicInteger();
            AtomicInteger end = new AtomicInteger();
            // 已归档流程
            // 分批查询缓解内存上涨
            int offset = 0;
            List<TbKnowledgeBaseDataDTO> batch = new ArrayList<>();
            List<TbKnowledgeBaseDataDTO> temp = new ArrayList<>();

            do {
                temp = tbKnowledgeBaseDataDao.getBaseKeyByTypePaged(templateType, offset, BATCH_SIZE);
                batch.addAll(temp);
                offset += BATCH_SIZE;

            } while (temp.size() == BATCH_SIZE); // 如果不足一批，说明结束
            List<String> archivedBaseKeyList = batch.
                    stream()
                    .map(TbKnowledgeBaseDataDTO::getKnowledge_base_key)
                    .collect(Collectors.toList());
            // 库是空的直接返回
            if(archivedBaseKeyList.isEmpty())
            {
                return ResultUtil.tableSuccess(page, pageSize, 0, new ArrayList<>());
            }
            if (functionFlag == 1 && archivedBaseKeyList != null && !archivedBaseKeyList.isEmpty()) {

                // 根据knowledge_base_key列表获取所有相关的TbFlowProcessMain对象
                Set<String> removeSet = new HashSet<>();
                List<TbFlowProcessMain> processMainList = tbFlowProcessMainDao.getFlowProcessMainsByBaseKeys(archivedBaseKeyList);
                if(!Objects.isNull(processMainList))
                {
                    removeSet = processMainList.stream().filter(p -> p.getStatus() != 3)
                            .map(TbFlowProcessMain::getKnowledge_base_key).collect(Collectors.toSet());
                }

                // 移除不需要的元素
                archivedBaseKeyList.removeIf(removeSet::contains);
            }

            // 获取器件编码和封装的 key
            String deviceCodeKey = null;
            String devicePackageKey = null;

            LinkedHashMap<String, List<TbKnowledgeBaseData>> groupedByFlowNo = new LinkedHashMap<>();
            AtomicInteger total = new AtomicInteger(0); // 总数量

            // 对表中，器件封装或者编码包含通配符的记录单独处理

            boolean containsDeviceKey = false;
            if(templateType.equals("deviceType")){

                deviceCodeKey =
                        tbTemplateDao.getTbTemplateKeyByName("deviceType", version, "器件编码");
                devicePackageKey =
                        tbTemplateDao.getTbTemplateKeyByName("deviceType", version, "器件封装");

                // 判断 filteredKeyValueMaps 是否包含 deviceCodeKey 或 devicePackageKey
                if (filteredKeyValueMaps != null && !filteredKeyValueMaps.isEmpty()) {
                    for (Map<String, String> condition : filteredKeyValueMaps) {
                        String key = condition.get("key");
                        if (key != null && (key.equals(deviceCodeKey) || key.equals(devicePackageKey))) {
                            containsDeviceKey = true;
                            break; // 找到一个匹配即可
                        }
                    }
                }
            }
            List<String> keyListBySearchFlowType = null;

            if(!StringUtils.isEmpty(searchFlowType))
            {
                switch (searchFlowType)
                {
                    case "器件vpl建库": searchFlowType = "vpl";break;
                    case "维护器件工艺知识库": searchFlowType = "device";break;
                    //case "维护工艺规范知识库": searchFlowType = "design";break;
                    default: break;
                }
                keyListBySearchFlowType = tbFlowProcessMainDao.getByFlowType(searchFlowType);
            }

            Set<String> keyListBySearchUser = null;
            if(!StringUtils.isEmpty(searchUser))
            {
                List<String> flowListBySearchUser = tbFlowProcessRecordsDao.getRecordByApproveUser(searchUser);
                keyListBySearchUser = tbFlowProcessMainDao.getFlowProcessMainByFlowList(flowListBySearchUser).stream()
                        .map(TbFlowProcessMain::getKnowledge_base_key).collect(Collectors.toSet());
            }

            if (hasValidContidionts) {
                    Map<String, List<Integer>> keyToMatchedIndexes = new HashMap<>();
                    // 数据库匹配查找
                    LinkedHashSet<String> filteredBaseKeySet = getFilteredByDataBase(
                        filteredKeyValueMaps, keyToMatchedIndexes);
                    if(keyListBySearchFlowType != null)
                    {
                        filteredBaseKeySet.retainAll(keyListBySearchFlowType);
                    }
                    if(keyListBySearchUser != null)
                    {
                        filteredBaseKeySet.retainAll(keyListBySearchUser);
                    }
                    total.set(filteredBaseKeySet.size());
                    getPaginationInfo(begin, pageSize, start, end, filteredBaseKeySet.size());
                    filteredBaseKeySet = subSet(filteredBaseKeySet, start.get(), end.get() + 1);
                    LinkedHashMap<String, List<TbKnowledgeBaseData>> finalGroupedByFlowNo = new LinkedHashMap<>();
                    try (Cursor<TbKnowledgeBaseData> cursor = tbKnowledgeBaseDataDao.scanAllByBaseKeys(
                            filteredBaseKeySet.stream().collect(Collectors.toList()))) {
                        cursor.forEach(data -> {
                            String flowNo = data.getFlow_no();
                            finalGroupedByFlowNo.computeIfAbsent(flowNo, k -> new ArrayList<>()).add(data);
                        });
                    } catch (IOException e) {
                        throw new RuntimeException("流式读取失败", e);
                    }
                    groupedByFlowNo = finalGroupedByFlowNo;
             }else {
                // 加载所有归档数据
                if(keyListBySearchFlowType != null)
                {
                    archivedBaseKeyList.retainAll(keyListBySearchFlowType);
                }
                if(keyListBySearchUser != null)
                {
                    archivedBaseKeyList.retainAll(keyListBySearchUser);
                }
                total.set(archivedBaseKeyList.size());
                getPaginationInfo(begin, pageSize, start, end, archivedBaseKeyList.size());
                List<String> pagedList  = new ArrayList<>(archivedBaseKeyList.subList(start.get(), end.get() + 1));
                LinkedHashMap<String, List<TbKnowledgeBaseData>> finalGroupedByFlowNo = new LinkedHashMap<>();
                try (Cursor<TbKnowledgeBaseData> cursor = tbKnowledgeBaseDataDao.scanAllByBaseKeys(pagedList)) {
                    //finalGroupedByFlowNo = groupedByFlowNo;
                    cursor.forEach(data -> {
                        String flowNo = data.getFlow_no();
                        finalGroupedByFlowNo.computeIfAbsent(flowNo, k -> new ArrayList<>()).add(data);
                    });
                } catch (IOException e) {
                    throw new RuntimeException("流式读取失败", e);
                }
                groupedByFlowNo = finalGroupedByFlowNo;
                log.info("after cursor foreach groupedByFlowNo size is " + groupedByFlowNo.size());
                //groupedByFlowNo = getDataByBaseKeyList(pagedList.stream().collect(Collectors.toSet()));
//                        tbKnowledgeBaseDataDao.
//                                getByBaseKeyList(archivedBaseKeyList.stream().collect(Collectors.toSet())).
//                                stream().
//                                collect(Collectors.groupingBy(TbKnowledgeBaseData::getFlow_no,
//                                        LinkedHashMap::new,
//                                        Collectors.toList()));
            }

            // 查询
            List<TbTemplate> paramKeysOfTemplate =
                    tbTemplateDao.getTbTemplateByVersion(latestVersion.getTemplate_type(), version);

            // 单次分组，过滤 null
            Map<String, List<TbTemplate>> groupedTemplates = new HashMap<>();
            for (TbTemplate template : paramKeysOfTemplate) {
                String parentParamKey = template.getParent_param_key();
                if (parentParamKey == null) {
                    continue;
                }
                groupedTemplates.computeIfAbsent(parentParamKey, k -> new ArrayList<>()).add(template);
            }

            // 提取结果列表
            List<TbTemplate> applicationTemplateList = groupedTemplates.getOrDefault("application", new ArrayList<>());
            List<TbTemplate> descriptionTemplateList = groupedTemplates.getOrDefault("description", new ArrayList<>());
            List<TbTemplate> baseTypeTemplateList = groupedTemplates.getOrDefault(templateType, new ArrayList<>());
            if(applicationTemplateList == null || descriptionTemplateList == null || baseTypeTemplateList == null)
            {
                return  ResultUtil.error(500, "工艺知识模板数据列缺失", "some template column is null", null);
            }
            // 获取最新模板所有列名集合
            List<String> applicationParamKeys = applicationTemplateList.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());

            List<String> descriptionParamKeys = descriptionTemplateList.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());

            List<String> baseTypeParamKeys = baseTypeTemplateList.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());
            AtomicInteger wildTotal = new AtomicInteger(0);
            JSONArray allResult = new JSONArray();
            if(containsDeviceKey)
            {
                // 对表中，器件封装或者编码包含通配符的记录单独处理
                List<TbKnowledgeBaseDataDTO> specialKeyList =
                        tbKnowledgeBaseDataDao.getValueContainWildcard(deviceCodeKey, devicePackageKey);
                Set<String> wildKeyList = specialKeyList.stream()
                        .map(TbKnowledgeBaseDataDTO::getKnowledge_base_key)
                        .collect(Collectors.toSet());
                if(!wildKeyList.isEmpty())
                {
                    List<TbKnowledgeBaseData> wildData = tbKnowledgeBaseDataDao.getByBaseKeyList(wildKeyList);
                    LinkedHashMap<String, List<TbKnowledgeBaseData>> wildGroupedByFlowNo = new LinkedHashMap<>();
                    wildGroupedByFlowNo = wildData.
                            stream().
                            collect(Collectors.groupingBy(TbKnowledgeBaseData::getFlow_no,
                                    LinkedHashMap::new,
                                    Collectors.toList()));
                    wildTotal.set(wildKeyList.size());
                    List<Map<String, String>> bomParams = keyValueMapsByFather.get(templateType);
                    transposeKnowledgeBaseData(wildGroupedByFlowNo, allResult, applicationParamKeys,
                            baseTypeParamKeys, descriptionParamKeys, templateType, functionFlag,
                            ruleMap, paramKeysOfTemplate, true, bomParams, wildTotal);
                }
            }
            // 使用 Collectors.groupingBy 进行分组
//            Map<String, List<TbKnowledgeBaseData>> groupedByBaseKey = dataList.stream()
//                    .collect(Collectors.groupingBy(TbKnowledgeBaseData::getKnowledge_base_key));
            // 遍历每个分组并进行处理
            JSONArray result = new JSONArray();
            //int curIndex = 0;
            transposeKnowledgeBaseData(groupedByFlowNo, result, applicationParamKeys, baseTypeParamKeys,
                    descriptionParamKeys, templateType, functionFlag, ruleMap, paramKeysOfTemplate,
                    false, null, total);

            allResult.addAll(result);
            // 追加可能导致数据超过页面限制数量
            if(allResult.size() > pageSize)
            {
                int offSetStart = (page - 1) * pageSize;
                int offSetEnd = offSetStart + pageSize;
                if(offSetEnd > allResult.size())
                {
                    offSetEnd = allResult.size();
                }
                List<Object> ultRet = allResult.subList(offSetStart, offSetEnd);
                return ResultUtil.tableSuccess(page, pageSize, total.get() + wildTotal.get(), ultRet);
            }
            return ResultUtil.tableSuccess(page, pageSize, total.get() + wildTotal.get(), allResult);
        }catch (Exception e){
            log.error("error : ", e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    // 将纵表数据转置
    public void transposeKnowledgeBaseData(
            LinkedHashMap<String, List<TbKnowledgeBaseData>> groupedByFlowNo,
            JSONArray result,
            List<String> applicationParamKeys,
            List<String> baseTypeParamKeys,
            List<String> descriptionParamKeys,
            String templateType,
            int functionFlag,
            Map<Integer, String> ruleMap,
            List<TbTemplate> paramKeysOfTemplate,
            boolean handleWild,
            List<Map<String, String>> keyValueMaps,
            AtomicInteger total) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 预存储数据
        Map<String, String> flowNoToVersion = new HashMap<>();
        List<String> flowNoList = groupedByFlowNo.keySet().stream()
                .collect(Collectors.toList());
        Map<String, List<TbFlowProcessRecords>> flowNoToRecords = new HashMap<>();
        Map<String, List<String>> versionToParents = new HashMap<>();
        Map<String, List<TbKnowledgeBasePushLogic>> baseKeyToLogics = new HashMap<>();
        Map<String, String> flowNoToFlowType = new HashMap<>();
        Map<Integer, User> userIdToUser = new HashMap<>();
        List<String> baseKeyList = new ArrayList<>();
        List<Integer> userIdList = new ArrayList<>();
        Map<String, TbFlowProcessMain> flowNoToProcessMain = new HashMap<>();
        // 按版本收集需要查询的 param_key
        Map<String, Set<String>> versionToParamKeys = new HashMap<>();

        // 第一个循环：处理 groupedByFlowNo 数据，收集批量查询所需参数
        for (Map.Entry<String, List<TbKnowledgeBaseData>> entry : groupedByFlowNo.entrySet()) {
            String curFlowNo = entry.getKey();
            log.info("Processing flow_no: {}", curFlowNo);

            String knowledgeBaseKey = entry.getValue().get(0).getKnowledge_base_key();

            baseKeyList.add(knowledgeBaseKey);
            String curVersion = entry.getValue().get(0).getVersion();

            // 分组数据
            List<TbKnowledgeBaseData> group = entry.getValue();
            List<TbKnowledgeBaseData> baseTypeDataList = group.stream()
                    .filter(item -> templateType.equals(item.getParent_param_key()))
                    .collect(Collectors.toList());
            List<TbKnowledgeBaseData> appDataList = group.stream()
                    .filter(item -> "application".equals(item.getParent_param_key()))
                    .collect(Collectors.toList());
            List<TbKnowledgeBaseData> descriptionDataList = group.stream()
                    .filter(item -> "description".equals(item.getParent_param_key()))
                    .collect(Collectors.toList());

            // 转换为 JSON 数组
            JSONArray appArray = addToTemplateDataArray(applicationParamKeys, appDataList, true);
            JSONArray baseTypeArray = addToTemplateDataArray(baseTypeParamKeys, baseTypeDataList, false);
            JSONArray descriptArray = addToTemplateDataArray(descriptionParamKeys, descriptionDataList, true);
            if (handleWild && "deviceType".equals(templateType)) {
                filtByKeyValueMaps(baseTypeArray, keyValueMaps, curVersion, templateType);
            }
            if (handleWild && baseTypeArray.isEmpty()) {
                total.set(total.decrementAndGet());
                continue;
            }

            // 计算最大行数
            int maxRow = Math.max(appArray.size(), Math.max(baseTypeArray.size(), descriptArray.size()));

            // 补齐数组到 maxRow
            addDataRowToMaxRow(appArray, applicationParamKeys, maxRow, true);
            addDataRowToMaxRow(baseTypeArray, baseTypeParamKeys, maxRow, false);
            addDataRowToMaxRow(descriptArray, descriptionParamKeys, maxRow, true);

            // 收集需要转换的 param_key，按版本分组
            Set<String> paramKeys = versionToParamKeys.computeIfAbsent(curVersion, k -> new HashSet<>());
            for (TbKnowledgeBaseData data : group) {
                paramKeys.add(data.getParam_key());
            }
            // 构造结果
            for (int i = 0; i < maxRow; i++) {
                JSONObject temp = new JSONObject();
                temp.put("knowledge_base_key", knowledgeBaseKey);
                temp.put("flow_no", curFlowNo);
                temp.putAll(appArray.getJSONObject(i));
                temp.putAll(descriptArray.getJSONObject(i));
                temp.putAll(baseTypeArray.getJSONObject(i));
                temp.put("is_default_push", entry.getValue().get(0).getIs_default_push());
                result.add(temp);
            }
        }

        // 批量查询 TbTemplate，按版本分组
        Map<String, Map<String, TbTemplate>> templateMap = new HashMap<>();
        for (Map.Entry<String, Set<String>> entry : versionToParamKeys.entrySet()) {
            String version = entry.getKey();
            List<String> paramKeys = new ArrayList<>(entry.getValue());
            if (!paramKeys.isEmpty()) {
                List<TbTemplate> templates = tbTemplateDao.getValuesByParamKeys(templateType, version, paramKeys);
                Map<String, TbTemplate> paramKeyToTemplate = templates.stream()
                        .collect(Collectors.toMap(TbTemplate::getParam_key, Function.identity(), (existing, replacement) -> existing));
                templateMap.put(version, paramKeyToTemplate);
            }
        }

        // 批量查询其他数据
        log.info("Fetching versions for flowNoList: {}", flowNoList);
        List<Map<String, Object>> versionList = tbKnowledgeBaseDataDao.getVersionByFlowNoList(flowNoList);
        for (Map<String, Object> versionMap : versionList) {
            String flowNo = (String) versionMap.get("flow_no");
            String version = (String) versionMap.get("version");
            flowNoToVersion.put(flowNo, version);
        }

        log.info("Fetching parents for versions: {}", flowNoToVersion.values());
        versionToParents = tbTemplateDao.getVersionToNotValueByParamKeys(
                templateType,
                new HashSet<String>(flowNoToVersion.values()),
                paramKeysOfTemplate.stream().map(TbTemplate::getParam_key).collect(Collectors.toList())
        ).stream().collect(Collectors.groupingBy(
                TbTemplate::getVersion,
                Collectors.mapping(TbTemplate::getParam_key, Collectors.toList())
        ));

        flowNoToRecords = tbFlowProcessRecordsDao.getFlowRecordsByFlowNo(flowNoList).stream()
                .collect(Collectors.groupingBy(TbFlowProcessRecords::getFlow_no));

        log.info("Fetching logics for baseKeyList: {}", baseKeyList);
        baseKeyToLogics = tbKnowledgeBasePushLogicDao.getKeyToLogicsByBaseKeyList(baseKeyList).stream()
                .collect(Collectors.groupingBy(TbKnowledgeBasePushLogic::getKnowledge_base_key));

        flowNoToFlowType = tbTaskVersionDao.getTaskVersionByFlowNoList(flowNoList).stream()
                .collect(Collectors.toMap(t -> t.getFlow_no(), t -> t.getTask_type()));

        flowNoToProcessMain = tbFlowProcessMainDao.getFlowProcessMainByFlowList(flowNoList)
                .stream().collect(Collectors.toMap(p -> p.getFlow_no(), Function.identity()));

        // 收集所有需要查询的 userId
        for (List<TbFlowProcessRecords> records : flowNoToRecords.values()) {
            if (records != null && !records.isEmpty()) {
                records.stream()
                        .filter(r -> r.getFlow_approver() != null)
                        .map(TbFlowProcessRecords::getFlow_approver)
                        .map(Integer::valueOf)
                        .forEach(userIdList::add);
            }
        }
        userIdList = userIdList.stream().distinct().collect(Collectors.toList());
        if (!userIdList.isEmpty()) {
            log.info("Fetching users for userIdList: {}", userIdList);
            userDao.getUserInfoByIdList(userIdList).forEach(user -> userIdToUser.put(user.getUserId(), user));
        }

        // 第二个循环：处理 details 和 logics 相关逻辑并填充结果
        for (Object o : result) {
            JSONObject jObj = (JSONObject) o;
            String flowNo = jObj.getString("flow_no");
            String version = flowNoToVersion.getOrDefault(flowNo, null);
            String baseKey = jObj.getString("knowledge_base_key");

            List<TbKnowledgeBasePushLogic> logics = baseKeyToLogics.getOrDefault(baseKey, Collections.emptyList());
            TbFlowProcessMain processMain = flowNoToProcessMain.getOrDefault(flowNo, new TbFlowProcessMain());

            // 处理推送逻辑
            String ruleDetail = "";
            if (!logics.isEmpty()) {
                Map<Integer, String> ruleDetailMap = new TreeMap<>();
                for (TbKnowledgeBasePushLogic l : logics) {
                    String rule = ". " + l.getBoardParamTypeName() + " " +
                            l.getBoardParamName() + " " +
                            ruleMap.get(l.getLogicRule()) + " " +
                            l.getLogicValue() + "\n";
                    ruleDetailMap.put(l.getSort() + 1, rule);
                }
                for (Map.Entry<Integer, String> ruleEntry : ruleDetailMap.entrySet()) {
                    ruleDetail += ruleEntry.getKey() + ruleEntry.getValue();
                }
            }
            jObj.put("knowledgeBaseLogic", logics);
            jObj.put("ruleDetail", ruleDetail);

            // 处理 日志信息 相关逻辑
            User functionUser = null;
            User approveUser = null;
            User creater = null;
            Date updateTime = null;
            Date createTime = null;
            TbFlowProcessRecords minLevelRecord;
            TbFlowProcessRecords maxLevelRecord;
            // 可能是维护过的知识,knowledge_base_key是不会变更的
            List<TbFlowProcessMain> mainList = tbFlowProcessMainDao.getFlowProcessMainByBaseKey(baseKey);

            // 最新维护的流程
            if(mainList.size() > 1)
            {
                String flowMainFunction = mainList.get(0).getFlow_no();
                List<TbFlowProcessRecords> funcRecords = tbFlowProcessRecordsDao.getRecordByFlowNo(flowMainFunction);
                minLevelRecord = funcRecords.stream()
                        .min(Comparator.comparingInt(TbFlowProcessRecords::getFlow_level))
                        .orElse(null);
                // 原始创建流程
                String flowMainCreate = mainList.get(mainList.size() - 1).getFlow_no();
                List<TbFlowProcessRecords> createRecords = tbFlowProcessRecordsDao.getRecordByFlowNo(flowMainCreate);
                maxLevelRecord = createRecords.stream()
                        .max(Comparator.comparingInt(TbFlowProcessRecords::getFlow_level))
                        .orElse(null);
            }else{
                List<TbFlowProcessRecords> records = flowNoToRecords.getOrDefault(flowNo, Collections.emptyList());
                // 查找 flow_level 最小的记录并设置 functionUser 维护人
                minLevelRecord = records.stream()
                        .min(Comparator.comparingInt(TbFlowProcessRecords::getFlow_level))
                        .orElse(null);

                // 查找 flow_level 最大的记录并设置 creater 和 createTime 创建人 和 创建时间
                maxLevelRecord = records.stream()
                        .max(Comparator.comparingInt(TbFlowProcessRecords::getFlow_level))
                        .orElse(null);
            }
            if (minLevelRecord != null) {

                functionUser = userIdToUser.get(Integer.valueOf(minLevelRecord.getFlow_approver()));
                updateTime = minLevelRecord.getCreate_time();
            }

            if(maxLevelRecord != null){
                 creater = userIdToUser.get(Integer.valueOf(maxLevelRecord.getFlow_approver()));
                 createTime = maxLevelRecord.getCreate_time();
            }

            // 填充 details 相关字段
            jObj.put("function_user", functionUser);
            jObj.put("update_time", updateTime != null ? sdf.format(updateTime) : null);
            jObj.put("approve_user", approveUser);
            jObj.put("create_time", createTime != null ? sdf.format(createTime) : null);
            jObj.put("create_user", creater);
            jObj.put("flow_type", flowNoToFlowType.get(flowNo));
            jObj.put("status", Objects.equals(processMain.getStatus(), 3) ? "已归档" : "维护中");

            // 调用优化后的 transParamKeyToRealValue
            transParamKeyToRealValue(version, templateType, jObj, versionToParents.get(version), templateMap);
        }

        log.info("Result size is {}", result.size());
    }
    private void getPaginationInfo(int begin, int pageSize, AtomicInteger start, AtomicInteger end, int size) {
        // 如果 size = 0，直接返回空范围
        if (size == 0) {
            start.set(0);
            end.set(-1); // 确保 end.get() + 1 = 0，符合 subSet 的 toIndex <= size
            return;
        }

        // 处理无效输入（begin < 0 或 pageSize <= 0）
        if (begin < 0 || pageSize <= 0) {
            start.set(0);
            end.set(size - 1);
            return;
        }

        // 设置初始的 start 和 end 值
        start.set(begin);
        end.set(begin + pageSize - 1);

        // 确保 start 在合法范围内
        if (start.get() >= size) {
            start.set(0);
            end.set(-1); // 空范围，end.get() + 1 = 0
            return;
        }

        // 确保 end 在合法范围内且不小于 start
        if (end.get() >= size) {
            end.set(size - 1);
        }
        if (end.get() < start.get()) {
            end.set(start.get());
        }
    }
    public List<TbKnowledgeBaseFile> transferByFileParamKey(String paramValue){
        List<TbKnowledgeBaseFile> ret = null;

        if (paramValue != null) {
            List<String> fileList = new ArrayList<>(Arrays.asList(paramValue.split(",")));
            ret = tbKnowledgeBaseFileDao.getTbKnowledgeBaseFileByParams(fileList);
            ret.stream()
                    .filter(file -> file.getFile_type() != null && file.getFile_type() == 4)
                    .forEach(file -> {
                        String base64;
                        if (env.equals("dev"))
                        {
                            base64 = s3UploadFileService.getImageBase64FromUrl(file.getUrl());
                        }
                        else
                        {
                            base64 = s3UploadFileService.getImageBase64FromUrl(url + "/" + fileBucket + "/" + file.getUrl());
                        }
                        file.setUrl(CommonUtils.base64Prefix()+base64);
                    });
        }

        return ret;
    }
    private static JSONArray extractRows(List<String> baseKeyList, Map<String, List<JSONObject>> groupMap, Integer begin, Integer pageSize) {
        int start;
        int end;
        if(Objects.isNull(begin) && Objects.isNull(pageSize))
        {
            start = 0;
            end = baseKeyList.size() - 1;
        }else{
            // 确定要提取的行范围
            start = begin;
            end = begin + pageSize - 1;
        }

        // 边界检查
        if (start >= baseKeyList.size()) {
            return new JSONArray(); // 如果起始索引超出范围，返回空数组
        }
        if (end >= baseKeyList.size()) {
            end = baseKeyList.size() - 1; // 如果结束索引超出范围，调整到最后一行
        }

        // 第四步：提取指定范围的 knowledge_base_key
        List<String> selectedbaseKeys = new ArrayList<>(baseKeyList.subList(start, end + 1));

        // 第五步：使用 Stream 收集所有选中的 JSONObject
        JSONArray selectedArray = selectedbaseKeys.stream()
                .flatMap(baseKey -> groupMap.get(baseKey).stream()) // 将每个 knowledge_base_key 的 List 展平
                .collect(Collectors.toCollection(JSONArray::new)); // 收集到新的 JSONArray

        return selectedArray;
    }
    @Override
    public Result queryExcipientsDataBylimit(List<Map<String, String>> keyValueMaps, int page, int pageSize){
        try{
            String templateType = "materialType";
            TbTemplateVersion param = new TbTemplateVersion();
            param.setTemplate_type(templateType);
            List<TbTemplateVersion> list = tbTemplateVersionDao.getTemplateVersionList(param);
            // 使用Comparator来比较版本号
//            Comparator<TbTemplateVersion> versionComparator = (o1, o2) -> {
//                String v1 = o1.getVersion();
//                String v2 = o2.getVersion();
//
//                return compareVersions(v1, v2);
//            };
//
//            // 找出最新的模板版本
//            TbTemplateVersion latestVersion = list.stream()
//                    .max(versionComparator)
//                    .orElse(null);
//
//            if (latestVersion == null) {
//                log.error("cannot find newest template");
//                return ResultUtil.error(500, "未能找到最新版本模板", "cannot find newest template", null);
//            }
//            log.info("latest version is " + latestVersion.getVersion());
            // 根据模板类型和前端的keyValueMaps进一步筛选查询结果
            // 所有key和value都为null相当于没有过滤
            // key不为空value为空相当于无效过滤，去掉
            boolean hasValidContidionts = true;
            List<Map<String, String>> filteredKeyValueMaps = keyValueMaps.stream()
                    .filter(map -> {
                        String key = map.get("key");
                        String value = map.get("value");
                        return key != null && !key.isEmpty() && value != null && !value.isEmpty();
                    })
                    .collect(Collectors.toList());
            if(filteredKeyValueMaps.size() == 0)
            {
                hasValidContidionts = false;
            }

            // 先过滤已归档流程
            List<String> archivedFlowList;

            List<TbExcipientsData> allExcipientsData;

            archivedFlowList = tbTaskVersionDao.getArchivedFlows("material");
            if(archivedFlowList == null)
            {
                return ResultUtil.success(new ArrayList<>());
            }
            if(hasValidContidionts)
            {
                // 如果查询键值对有效，直接根据流程号获取所有对象
//                List<String> archivedFlowSet = Optional.ofNullable(archivedFlowList)
//                        .orElse(Collections.emptyList());
//                Set<String> commonFlowNos = findCommonFlowNosOfExcipientsData(filteredKeyValueMaps, archivedFlowSet);
//                // 使用交集中的流程号来获取相应数据并添加到 allTaskDataByTemplate 中
//                for (String no : commonFlowNos) {
//                    // 添加所有有效流程的数据到 allTaskDataByTemplate
//                    allExcipientsData.addAll(tbExcipientsDataDao.getExcipientsDataByFlowNo(no));
//                }
                allExcipientsData = tbExcipientsDataDao.getAll();
            }else
            {
                // 如果无效，需要所有归档数据
//                for (String f : archivedFlowList) {
//                    allExcipientsData.addAll(tbExcipientsDataDao.getExcipientsDataByFlowNo(f));
//                }
                allExcipientsData = tbExcipientsDataDao.getAll();
            }

            if(allExcipientsData.size() == 0)
            {
                log.error("template flow data is null");
                return ResultUtil.success(new ArrayList<>());
            }

            // 当前最新模板的所有param_key表,查询按照sort字段升序
            List<TbTemplate> paramKeysOfTemplate =
                    tbTemplateDao.getTbTemplateByLimit(templateType, null, 1/*latestVersion.getTemplate_type(), latestVersion.getVersion()*/);

            // 获取最新模板所有列名集合
            List<String> excipientsParamKeys = paramKeysOfTemplate.
                    stream().
                    map(TbTemplate::getParam_key).
                    collect(Collectors.toList());
            // 使用 Collectors.groupingBy 进行分组
//            Map<String, List<TbExcipientsData>> groupedByFlowNo = allExcipientsData.stream()
//                    .collect(Collectors.groupingBy(TbExcipientsData::getFlowNo));
//
//            log.info("flows match status '3' size is " + groupedByFlowNo.size());
            // 1. 按 rowSort 分组
            Map<Integer, List<TbExcipientsData>> rowDataMap = allExcipientsData.stream()
                    .collect(Collectors.groupingBy(TbExcipientsData::getRowSort));

            // 2. 为每个分组计算“代表性的 sort 值”：取非 null 的最小值；若全为 null，则为 null
            //    （也可以取任意非 null 值，但取 min 更稳健）
            Function<List<TbExcipientsData>, Integer> extractGroupSort = group -> {
                return group.stream()
                        .map(TbExcipientsData::getSort) // getSort() 返回 Integer（可为 null）
                        .filter(Objects::nonNull)
                        .min(Integer::compareTo)
                        .orElse(null); // 全为 null 时返回 null
            };

            // 3. 排序分组
            List<Map.Entry<Integer, List<TbExcipientsData>>> sortedGroups = rowDataMap.entrySet()
                    .stream()
                    .sorted(Comparator
                            .comparing(
                                    (Map.Entry<Integer, List<TbExcipientsData>> e) -> extractGroupSort.apply(e.getValue()),
                                    Comparator.nullsLast(Integer::compareTo) // null 排最后
                            )
                            .thenComparing(Map.Entry::getKey) // sort 相同时，rowSort 升序
                    )
                    .collect(Collectors.toList());
            // 遍历每个分组并进行处理
            JSONArray valueArray = new JSONArray();
            for (Map.Entry<Integer, List<TbExcipientsData>> entry : sortedGroups){
                List<TbExcipientsData> group = entry.getValue();
                JSONObject temp = new JSONObject(true);
                temp.put("sort", group.get(0).getSort());
                temp.put("row_sort", String.valueOf(entry.getKey()));

                // 按辅料参数初始化空值
                for (String key : excipientsParamKeys) {
                    temp.put(key, null);
                }

                if (group.isEmpty()) {
                    valueArray.add(temp);
                    continue;
                }

                // === 1. 创建时间 & 创建人：使用第一条记录（或可改为最早创建时间） ===
                TbExcipientsData first = group.get(0);
                temp.put("createTime", first.getCreateTime());

                String creatorDisplayName = "";
                if (!StringUtils.isEmpty(first.getCreateUser())) {
                    User creator = userDao.getUserInfoById(Integer.valueOf(first.getCreateUser()));
                    if (creator != null) {
                        creatorDisplayName = creator.getDisplayName() + "/" + creator.getDomainAccount();
                    }
                }
                temp.put("createUser", creatorDisplayName);

                // === 2. 更新时间 & 更新人：找 updateTime 最晚的那条记录 ===
                TbExcipientsData latestUpdate = null;
                for (TbExcipientsData dt : group) {
                    if (dt.getUpdateTime() != null) {
                        if (latestUpdate == null || dt.getUpdateTime().after(latestUpdate.getUpdateTime())) {
                            latestUpdate = dt;
                        }
                    }
                }

                if (latestUpdate != null) {
                    temp.put("updateTime", latestUpdate.getUpdateTime());

                    String updaterDisplayName = "";
                    if (!StringUtils.isEmpty(latestUpdate.getUpdateUser())) {
                        User updater = userDao.getUserInfoById(Integer.valueOf(latestUpdate.getUpdateUser()));
                        if (updater != null) {
                            updaterDisplayName = updater.getDisplayName() + "/" + updater.getDomainAccount();
                        }
                    }
                    temp.put("updateUser", updaterDisplayName);
                } else {
                    // 如果都没更新时间，可以用创建时间兜底（可选）
                    temp.put("updateTime", "");
                    temp.put("updateUser", "");
                }

                // === 3. 填充参数值 ===
                for (TbExcipientsData dt : group) {
                    TbTemplate tp = tbTemplateDao.getValueByParamKey("materialType", null, dt.getParamKey());
                    if (tp != null && tp.getStatus() == 1) {
                        temp.put(dt.getParamKey(), dt.getParamValue());
                    }
                }

                valueArray.add(temp);
            }
            filtByKeyValueMapsForMapObj(valueArray, filteredKeyValueMaps);
            if(page < 0 && pageSize < 0)
            {
                return ResultUtil.success(valueArray);
            }
            int begin = (page - 1) * pageSize;
            int end = begin + pageSize;
            if(begin > valueArray.size() - 1)
            {
                return ResultUtil.success(new JSONArray());
            }
            if(end > valueArray.size() - 1)
            {
                end = valueArray.size() - 1;
            }
            JSONObject ultData = new JSONObject();
            int total = valueArray.size();

            return ResultUtil.tableSuccess(page, pageSize, total, valueArray.subList(begin, end + 1));
        }catch (Exception e){
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result deleteExcipientsParams(List<Integer> idList)
    {
        try{
            int sum = 0;
            int count = 0;
            for (Integer id : idList) {
                List<TbExcipientsData> dataList =
                        tbExcipientsDataDao.getAll();
                Map<Integer, List<TbExcipientsData>> rowDataMap = dataList.stream()
                        .collect(Collectors.groupingBy(TbExcipientsData::getRowSort,
                                LinkedHashMap::new, Collectors.toList()));
                TbTemplate tp = tbTemplateDao.getById(id);
                count += tbExcipientsDataDao.deleteByParamKey(tp.getParam_key());
                sum += tbTemplateDao.deleteById(id);
            }
            System.out.println("affected param rows : " + sum);
            log.debug("affected param rows : " + sum);
            System.out.println("affected data rows : " + count);
            log.debug("affected data rows : " + count);
            return ResultUtil.success("删除成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result updateExcipientsParams(List<TbTemplate> newTemplates){
        try{
            int sum = 0;
            int count = 0;
            // 参数有更新，所有行数据都要插入该参数预留
            List<TbExcipientsData> dataList =
                    tbExcipientsDataDao.getAll();
            Map<Integer, List<TbExcipientsData>> rowDataMap = dataList.stream()
                    .collect(Collectors.groupingBy(TbExcipientsData::getRowSort,
                            LinkedHashMap::new, Collectors.toList()));
            for (TbTemplate tp : newTemplates) {
                sum += tbTemplateDao.updateTemplate(tp);
                for (Map.Entry<Integer, List<TbExcipientsData>> entry : rowDataMap.entrySet()) {
                    TbExcipientsData excipientsData = new TbExcipientsData();
                    // 逐个字段设置
                    excipientsData.setTemplateType("materialType");
                    excipientsData.setParamNameEn("");
                    excipientsData.setParamKey(tp.getParam_key());
                    excipientsData.setParamName(tp.getParam_name());
                    excipientsData.setParamValue("");
                    excipientsData.setRowSort(entry.getKey());
                    excipientsData.setUpdateTime(new Date());
                    excipientsData.setUpdateUser(UserHeaderHolder.getUserId());
//            excipientsData.setParamType(json.getInteger("param_type"));
//            excipientsData.setLevel(json.getInteger("level"));
//            excipientsData.setSelectLevel(json.getInteger("select_level"));
//            excipientsData.setParentSelectId(json.getString("parent_select_id"));
//            excipientsData.setTip(json.getString("tip"));
//            excipientsData.setRemark(json.getString("remark"));
//                excipientsData.setCreateUser(userId);
//                excipientsData.setCreateTime(now);
                    count += tbExcipientsDataDao.updateByParamKey(excipientsData);
                }
            }

            System.out.println("affected param rows : " + sum);
            log.debug("affected param rows : " + sum);
            System.out.println("affected data rows : " + count);
            log.debug("affected data rows : " + count);
            return ResultUtil.success("参数更新成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result insertExcipientsParams(List<TbTemplate> newTemplates){
        try{
            tbTemplateDao.insertTemplateBatch(newTemplates);
            // 参数有新插入，所有行数据都要插入该参数预留
            List<TbExcipientsData> dataList =
            tbExcipientsDataDao.getAll();
            Map<Integer, List<TbExcipientsData>> rowDataMap = dataList.stream()
                    .collect(Collectors.groupingBy(TbExcipientsData::getRowSort,
                            LinkedHashMap::new, Collectors.toList()));
            for(TbTemplate tp : newTemplates){
                for (Map.Entry<Integer, List<TbExcipientsData>> entry : rowDataMap.entrySet()) {
                    TbExcipientsData excipientsData = new TbExcipientsData();
                    // 逐个字段设置
                    excipientsData.setTemplateType("materialType");
                    excipientsData.setParamNameEn("");
                    excipientsData.setParamKey(tp.getParam_key());
                    excipientsData.setParamName(Objects.isNull(tp) ? null : tp.getParam_name());
                    excipientsData.setParamValue("");
                    excipientsData.setRowSort(entry.getKey());
//            excipientsData.setParamType(json.getInteger("param_type"));
//            excipientsData.setLevel(json.getInteger("level"));
//            excipientsData.setSelectLevel(json.getInteger("select_level"));
//            excipientsData.setParentSelectId(json.getString("parent_select_id"));
//            excipientsData.setTip(json.getString("tip"));
//            excipientsData.setRemark(json.getString("remark"));
//                excipientsData.setCreateUser(userId);
//                excipientsData.setCreateTime(now);
                    tbExcipientsDataDao.insert(excipientsData);
                }
            }
            return ResultUtil.success("参数插入成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result insertOrUpdateToExcipientsDataByExcel(MultipartFile file, boolean insertData){
        try {
            // 解析Excel文件，获取批量数据
            List<LinkedHashMap<String, String>> batchData = ExcelUtils.parseExcelSheet0(file);
            // 检查数据是否为空
            if (Objects.isNull(batchData) || batchData.size() == 0) {
                return ResultUtil.error(500, "表格没有辅料数据", "no excipients data in the chart", null);
            }

            // 定义模板类型
            String templateType = "materialType";
            // 获取最新版本的所有模板参数
            List<TbTemplate> templateParams = tbTemplateDao.getTbTemplateByLimit(templateType, null, 1);
            // 提取参数名称列表
            List<String> paramNames = templateParams.stream()
                    .map(TbTemplate::getParam_name)
                    .collect(Collectors.toList());
            List<String> paramKeys = templateParams.stream()
                    .map(TbTemplate::getParam_key)
                    .collect(Collectors.toList());

            // 获取当前用户ID和当前时间
            String userId = UserHeaderHolder.getUserId();
            Date curTime = new Date();

            // 创建用于存储所有数据的列表
            List<TbExcipientsData> dataList = new ArrayList<>();

            // 根据addData参数决定是追加还是覆盖
            if (!insertData) {
                // 如果addData为false，清除所有现有数据
                tbExcipientsDataDao.deleteAll();
            }
            // row_sort最大值,相当于横表id
            Integer maxRowSort = tbExcipientsDataDao.getMaxRowSort();
            maxRowSort = Objects.isNull(maxRowSort) ? 0 : maxRowSort;

            // 处理导入的数据
            for (int i = 0; i < batchData.size(); i++) {
                // 获取当前行数据
                LinkedHashMap<String, String> rowData = batchData.get(i);

                // 遍历所有参数名称
                for (int j = 0; j < paramNames.size(); j++) {
                    String paramName = paramNames.get(j);
                    // 创建新的辅料数据对象
                    TbExcipientsData dt = new TbExcipientsData();
                    dt.setTemplateType(templateType);    // 设置模板类型
                    dt.setParamName(paramName);          // 设置参数名称
                    dt.setParamKey(paramKeys.get(j));
                    dt.setParamValue(rowData.getOrDefault(paramName, null));  // 设置参数值，若无则为null
                    if(StringUtils.isEmpty(dt.getParamValue()))
                    {
                        log.warn("param name " + dt.getParamName() + " null value");
                    }
                    dt.setCreateTime(curTime);           // 设置创建时间
                    dt.setCreateUser(userId);            // 设置创建用户
                    dt.setRowSort(maxRowSort + i + 1);   // 设置索引，相当于id
                    String sort = rowData.getOrDefault("排序", null);
                    if (NumberUtils.isNumber(sort) && NumberUtils.toInt(sort, Integer.MIN_VALUE) != Integer.MIN_VALUE)
                    {
                        dt.setSort(Integer.parseInt(sort));                        // 每行的排序
                    }
                    // 添加到数据列表
                    dataList.add(dt);
                }
            }

            // 执行批量插入操作（无论追加还是覆盖，最终都是插入新数据）
            tbExcipientsDataDao.batchInsert(dataList);

            // 返回成功结果
            return ResultUtil.success("导入数据成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            e.printStackTrace();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result deleteExcipientData(Integer rowSort){
        try{
            int count = tbExcipientsDataDao.deleteByRowSort(rowSort);
            System.out.println("influence count is " + count);
            log.debug("influence count is " + count);
            return ResultUtil.success("删除成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result deleteExcipientDataList(List<Integer> idList)
    {
        try{
            int sum = 0;
            for (Integer id : idList) {
                sum += tbExcipientsDataDao.deleteById(id);
            }
            System.out.println("affected rows : " + sum);
            log.debug("affected rows : " + sum);
            return ResultUtil.success("批量删除成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result updateExcipientData(List<TbExcipientsData> excipientsDataList){
        try{
            int sum = 0;
            for(TbExcipientsData dt : excipientsDataList)
            {
                sum += tbExcipientsDataDao.updateByParamKey(dt);
            }
            System.out.println("update count is " + sum);
            log.debug("update count is " + sum);
            return ResultUtil.success("参数更新成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error : ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    @Override
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public Result insertExcipientData(List<TbExcipientsData> excipientsDataList){
        try{
            int count = 0;
            for(TbExcipientsData dt : excipientsDataList)
            {
                // 行id
                Integer maxRowSort = tbExcipientsDataDao.getMaxRowSort();
                maxRowSort = Objects.isNull(maxRowSort) ? 0 : maxRowSort;
                // 行排序
                Integer maxSort = tbExcipientsDataDao.getMaxSort();
                maxSort = Objects.isNull(maxSort) ? 0 : maxSort;
                dt.setRowSort(maxRowSort + 1);
                dt.setSort(maxSort + 1);
            }
            tbExcipientsDataDao.batchInsert(excipientsDataList);
            System.out.println("influence count is " + count);
            return ResultUtil.success("参数插入成功");
        }catch(Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("error: ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    private List<TbKnowledgeBaseFile> specialParamTypeProcess(TbKnowledgeBaseData data) {
        List<TbKnowledgeBaseFile> ret = null;


        if (data.getParam_value() != null) {
            List<String> fileList = new ArrayList<>(Arrays.asList(data.getParam_value().split(",")));
            ret = tbKnowledgeBaseFileDao.getTbKnowledgeBaseFileByParams(fileList);
            ret.stream()
                    .filter(file -> file.getFile_type() != null && file.getFile_type() == 4)
                    .forEach(file -> {
                        String base64;
                        if (env.equals("dev"))
                        {
                            base64 = s3UploadFileService.getImageBase64FromUrl(file.getUrl());
                        }
                        else
                        {
                            base64 = s3UploadFileService.getImageBase64FromUrl(url + "/" + fileBucket + "/" + file.getUrl());
                        }
                        file.setUrl(CommonUtils.base64Prefix()+base64);
                    });
        }

        return ret;
    }
    @Override
    public Result outPutExipientData(List<Map<String, String>> keyValueMaps){
        try {
//            final String userId = UserHeaderHolder.getUserId();
//            OperateLog operateLog = new OperateLog(2,Integer.parseInt(userId) ,"板材能力导出",5,1,new Date(),UserHeaderHolder.getDomainAccount(),UserHeaderHolder.getIpAddress(),UserHeaderHolder.getNetworkSeg());
//            boolean aa = operateLogService.addOperateLog(operateLog);
            //List<TbTemplate> allParams =  tbTemplateDao.getTbTemplateByLimit("material", null, 1);
            Workbook workbook = new XSSFWorkbook();
            // 创建工作表对象
            Sheet sheet = workbook.createSheet("Sheet1");
            XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();
            setBorder.setBorderBottom(BorderStyle.THIN);
            setBorder.setBorderLeft(BorderStyle.THIN);
            setBorder.setBorderRight(BorderStyle.THIN);
            setBorder.setBorderTop(BorderStyle.THIN);

            //创建字体 加粗
            XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
            XSSFFont font = (XSSFFont) workbook.createFont();
            font.setBold(true);
            font.setFontHeight(11);
            style.setFont(font);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            setBorder.setFont(font);

            XSSFCellStyle setBorder2 = (XSSFCellStyle) workbook.createCellStyle();
            setBorder2.setBorderBottom(BorderStyle.THIN);
            setBorder2.setBorderLeft(BorderStyle.THIN);
            setBorder2.setBorderRight(BorderStyle.THIN);
            setBorder2.setBorderTop(BorderStyle.THIN);
            XSSFCellStyle dateBoard = (XSSFCellStyle) workbook.createCellStyle();
            dateBoard.setBorderBottom(BorderStyle.THIN);
            dateBoard.setBorderLeft(BorderStyle.THIN);
            dateBoard.setBorderRight(BorderStyle.THIN);
            dateBoard.setBorderTop(BorderStyle.THIN);
            dateBoard.setVerticalAlignment(VerticalAlignment.CENTER);
            dateBoard.setAlignment(HorizontalAlignment.LEFT);
            dateBoard.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
            // 所有辅料数据
            Result data = queryExcipientsDataBylimit(keyValueMaps, 1, 99999);
            if(Objects.isNull(data))
            {
                return ResultUtil.error(500, "没有辅料数据", "null excipient data", null);
            }
            JSONArray allData;

            if(data.getData() != null)
            {
                JSONObject tableDataObject = new JSONObject((Map<String, Object>) data.getData());
                allData = tableDataObject.getJSONArray("rows");
            }else{
                return ResultUtil.error(500, "没有辅料数据", "null excipient data", null);
            }
            // 初始化 HashMap
            HashMap<Integer, String> rowIndex2MaterialName = new HashMap<>();
            rowIndex2MaterialName.put(0, "排序");
            HashMap<Integer, String> rowIndex2MaterialKey = new HashMap<>();
            rowIndex2MaterialKey.put(0, "排序");
            List<TbTemplate> materialParams = tbTemplateDao.getTbTemplateByLimit("materialType", null, 1);

            if(materialParams == null)
            {
                ResultUtil.error(500, "列表不存在列", "column is null in this page", null);
            }
            // 使用 Collections.sort 和自定义 Comparator 进行排序
            Collections.sort(materialParams, new Comparator<TbTemplate>() {
                @Override
                public int compare(TbTemplate o1, TbTemplate o2) {
                    // 比较两个对象的 sort 属性
                    return Integer.compare(o1.getSort(), o2.getSort());
                }
            });
            // 从查询的数据获取所有属性名称,key
            int nameIndex = 0;
            int k = 0;
            for (TbTemplate param : materialParams
            ) {
                rowIndex2MaterialName.put(++nameIndex, param.getParam_name());
                rowIndex2MaterialKey.put(++k, param.getParam_key());
            }

            for(int j = 0; j <= nameIndex + 4; j++)
            {
                sheet.setColumnWidth(j, 50 * 256);
            }
            // 设置各个属性名称作为excel列名
            Row row_t1 = sheet.createRow(0);
            for (Map.Entry<Integer, String> entry : rowIndex2MaterialName.entrySet()) {
                int columnIndex = entry.getKey();
                String headerText = entry.getValue();

                Cell cell = row_t1.createCell(columnIndex);
                cell.setCellValue(headerText);
                cell.setCellStyle(setBorder);
            }

            // 获取下一个可用的列索引（追加在最后）
            int nextColumnIndex = rowIndex2MaterialName.isEmpty() ? 0 :
                    rowIndex2MaterialName.keySet().stream().max(Integer::compareTo).orElse(0) + 1;

            // 定义审计字段（按顺序）
            Map<String, String> auditFields = new LinkedHashMap<>();
            auditFields.put("createTime", "创建时间");
            auditFields.put("createBy", "创建人");
            auditFields.put("updateTime", "更新时间");
            auditFields.put("updateBy", "更新人");

            // 写入审计字段表头
            int auditStartColumn = nextColumnIndex;
            int auditColIndex = auditStartColumn;
            for (String auditHeader : auditFields.values()) {
                Cell cell = row_t1.createCell(auditColIndex);
                cell.setCellValue(auditHeader);
                cell.setCellStyle(setBorder);
                auditColIndex++;
            }

            // 根据paramKey查找对应的属性值填充到excel表中
            for (int index = 0; index < allData.size(); index++) {
                JSONObject rowData = (JSONObject) allData.get(index);
                Row row = sheet.createRow(index + 1); // 从第 2 行开始（索引 1）
                int colIndex = 0;
                // 遍历表头映射，填充对应列
                for (Map.Entry<Integer, String> entry : rowIndex2MaterialKey.entrySet()) {
                    int columnIndex = entry.getKey();
                    String paramKey = entry.getValue();

                    Cell cell = row.createCell(columnIndex);
                    cell.setCellStyle(setBorder2); // 默认数据样式

                    // 特殊处理 "排序" 列
                    if (columnIndex == 0) {
                        //String rowSort = rowData.getString("row_sort");
                        cell.setCellValue(rowData.getString("sort"));
                        continue;
                    }

                    // 获取对应 param_key 的值
                    String value = rowData.getString(paramKey);
                    if (value == null) {
                        cell.setCellValue(""); // null 值设为空字符串
                    } else {
                        cell.setCellValue(value); // 直接设置字符串值
                    }
                    colIndex = columnIndex;
                }

                // 创建时间，创建人，更新时间，更新人
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                // 创建时间
                Date createTime = rowData.getDate("createTime");
                String formatCreateTime = createTime != null ? sdf.format(createTime) : "";
                Cell createTimeCell = row.createCell(++colIndex);
                createTimeCell.setCellStyle(setBorder2);
                createTimeCell.setCellValue(formatCreateTime);

                // 创建人
                String createUser = rowData.getString("createUser");
                Cell creatorCell = row.createCell(++colIndex);
                creatorCell.setCellStyle(setBorder2);
                creatorCell.setCellValue(createUser != null ? createUser : "");

                // 更新时间
                Date updateTime = rowData.getDate("updateTime");
                String formatUpdateTime = updateTime != null ? sdf.format(updateTime) : "";
                Cell updateTimeCell = row.createCell(++colIndex);
                updateTimeCell.setCellStyle(setBorder2);
                updateTimeCell.setCellValue(formatUpdateTime);

                // 更新人
                String updateUser = rowData.getString("updateUser");
                Cell updaterCell = row.createCell(++colIndex);
                updaterCell.setCellStyle(setBorder2);
                updaterCell.setCellValue(updateUser != null ? updateUser : "");

            }
            // 导出Excel文件
            String filename = "辅料库表" + String.valueOf(System.currentTimeMillis());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String format = sdf.format(new Date());
            File folder = new File(rootPath + "knowledgeBase\\" + format);
            if (!folder.isDirectory()) {
                folder.mkdirs();
            }
            String filePath = rootPath + "knowledgeBase\\" + format + "\\" + filename + ".xlsx";
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
            return ResultUtil.success(filePath);
        } catch (Exception e) {
            e.printStackTrace();
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }
    /**
     * Adds rows to a JSONArray until it reaches maxRow.
     *
     * @param array      the JSONArray to modify
     * @param paramKeys  the keys to populate in each JSONObject (used when special is true)
     * @param maxRow     the desired number of rows
     * @param special    if true, adds new empty JSONObjects; if false, copies the last row
     * @throws IllegalArgumentException if inputs are invalid
     */
    private void addDataRowToMaxRow(JSONArray array, List<String> paramKeys, int maxRow, boolean special) {
        // 输入校验
        Objects.requireNonNull(array, "JSONArray cannot be null");
        if (maxRow < 0) {
            throw new IllegalArgumentException("maxRow cannot be negative: " + maxRow);
        }
        int currentSize = array.size(); // 使用 length() 代替 size()，更符合 JSON API
        if (currentSize >= maxRow) {
            return; // 无需添加，直接返回
        }

        // 限制 maxRow，防止无限循环
        if (maxRow > 10000) { // 假设 10000 是一个合理的上限
            throw new IllegalArgumentException("maxRow exceeds maximum allowed value: " + maxRow);
        }

        if (special) {
            // 校验 paramKeys
            Objects.requireNonNull(paramKeys, "paramKeys cannot be null when special is true");
            for (int i = currentSize; i < maxRow; i++) {
                JSONObject jsonDt = new JSONObject();
                for (String paramKey : paramKeys) {
                    if (paramKey == null || paramKey.trim().isEmpty()) {
                        continue; // 跳过无效的键
                    }
                    jsonDt.put(paramKey, (Object) null); // 显式指定为 null
                }
                array.add(jsonDt);
            }
        } else {
            // 确保数组不为空
            JSONObject lastRow = new JSONObject();
            if (currentSize == 0) {
                for (String paramKey : paramKeys) {
                    if (paramKey == null || paramKey.trim().isEmpty()) {
                        continue; // 跳过无效的键
                    }
                    lastRow.put(paramKey, (Object) null); // 显式指定为 null
                }
            }else{
                // 获取最后一个对象，并进行深拷贝
                lastRow = array.getJSONObject(currentSize - 1);
            }
            for (int i = currentSize; i < maxRow; i++) {
                JSONObject jsonDt = deepCopy(lastRow); // 深拷贝
                array.add(jsonDt);
            }
        }
    }

    /**
     * Performs a deep copy of a JSONObject to avoid shared references.
     *
     * @param source the JSONObject to copy
     * @return a deep-copied JSONObject
     */
    private JSONObject deepCopy(JSONObject source) {
        if (source == null) {
            return new JSONObject();
        }
        return new JSONObject(source);
    }
    private void transParamKeyToRealValue(String curVersion, String templateType, JSONObject jsonObject,
                                          List<String> parents, Map<String, Map<String, TbTemplate>> templateMap) {
        // 按版本获取对应的 templateMap
        Map<String, TbTemplate> versionTemplateMap = templateMap.getOrDefault(curVersion, new HashMap<>());

        // 遍历 JSON 对象，更新实际值
        for (String key : jsonObject.keySet()) {
            if ("flow_no".equals(key)) {
                continue;
            }

            Object value = jsonObject.get(key);
            if (value instanceof String && parents.contains(key)) {
                String stringValue = (String) value;
                if (stringValue.contains("[") && stringValue.contains("]")) {
                    String formattedNodeValue;
                    List<String> nodeValueList;
                    try {
                        nodeValueList = JSON.parseArray(stringValue, String.class);
                        for (int i = 0; i < nodeValueList.size(); i++) {
                            TbTemplate child = versionTemplateMap.get(nodeValueList.get(i));
                            if (child != null) {
                                nodeValueList.set(i, child.getParam_name());
                            }
                        }
                        formattedNodeValue = String.join("\n", nodeValueList);
                        jsonObject.put(key, formattedNodeValue);
                    } catch (JSONException e) {
                        log.error("[transParamKeyToRealValue] 解析失败: {}", stringValue);
                    }
                    continue;
                }
                if (StringUtils.isEmpty(stringValue)) {
                    log.warn("[transParamKeyToRealValue] value is empty for key: {}", key);
                    continue;
                }
                TbTemplate child = versionTemplateMap.get(stringValue);
                if (child == null) {
                    log.warn("[transParamKeyToRealValue] no template found for param_key: {} in version: {}", stringValue, curVersion);
                    continue;
                }
                String newValue = child.getParam_name();
                log.info("[transParamKeyToRealValue] updating key: {} with value: {} in version: {}", key, newValue, curVersion);
                jsonObject.put(key, newValue);
            }
        }
    }

    /**
     * 根据 keyValueMaps 过滤 JSONArray 中的元素，保留符合条件的记录
     *
     * @param result       需要过滤的 JSONArray
     * @param bomParams 包含 key 和 value 的条件列表
     * @param version      模板版本信息，用于获取器件编码和器件封装的 key
     * @param templateType
     */
    private void filtByPartNoAndPackageName(JSONArray result, List<Map<String, String>> bomParams, String version, String templateType) {
        // 如果 result 或 bomParams 为空或 null，直接返回，不做处理
        if (result == null || result.isEmpty() || bomParams == null || bomParams.isEmpty()) {
            return;
        }

        // 获取器件编码和器件封装的 key
        String deviceCodeKey = null;
        String devicePackageKey = null;
        if (version != null) {
            deviceCodeKey = tbTemplateDao.getTbTemplateKeyByName(templateType, version, "器件编码");
            devicePackageKey = tbTemplateDao.getTbTemplateKeyByName(templateType, version, "器件封装");
            if (StringUtils.isEmpty(deviceCodeKey) || StringUtils.isEmpty(devicePackageKey)) {
                return;
            }
        } else {
            return;
        }

        // 缓存正则表达式的 Pattern 对象
        Map<String, Pattern> patternCache = new HashMap<>();

        // 使用索引遍历并移除不满足条件的元素
        for (int i = 0; i < result.size(); ) {
            Object obj = result.get(i);
            if (!(obj instanceof JSONObject)) {
                // 如果不是 JSONObject，直接移除
                result.remove(i);
                continue;
            }

            JSONObject jsonObject = (JSONObject) obj;
            boolean matchesAnyCondition = false;
            Map<String, String> matchedCondition = null;

            // 检查是否匹配 bomParams 中的任意一个条件
            for (Map<String, String> condition : bomParams) {
                String partNo = condition.get("partNo");
                if (partNo == null) {
                    continue; // 跳过无效条件
                }
                String codeValue = jsonObject.getString(deviceCodeKey);
                if (codeValue == null) {
                    continue; // 无器件编码值，无法匹配
                }

                // 检查 partNo 是否匹配
                boolean partNoMatch = matchesPattern(partNo, codeValue, patternCache);

                if (partNoMatch) {
                    // 编码含通配并且匹配 知识则匹配
                    if(codeValue.contains("*"))
                    {
                        matchesAnyCondition = true;
                        matchedCondition = condition;
                        break;
                    }
                    log.info("partNo is " + partNo + " match knowledgeBase partNo is " + codeValue);
                    String packageName = condition.get("packageName");
                    // packageName 不为空，需同时匹配
                    String packageValue = jsonObject.getString(devicePackageKey);
                    if (StringUtils.isEmpty(packageValue)) {

                        // packageName 为空，只需 partNo 匹配即可
                        matchesAnyCondition = true;
                        matchedCondition = condition;
                        break;
                    } else {
                        // 否则就都需匹配
                        if (packageValue != null && matchesPattern(packageName, packageValue, patternCache)) {
                            log.info("packageName is " + packageName + " match knowledgeBase packageName is " + packageValue);
                            matchesAnyCondition = true;
                            matchedCondition = condition;
                            break;
                        }
                    }
                }
            }

            // 如果匹配任意条件，扩充元素并保留；否则移除
            if (matchesAnyCondition) {
                // 扩充匹配的 partNo, posNo, packageName
                jsonObject.put("partNo", matchedCondition.get("partNo"));
                jsonObject.put("posNo", matchedCondition.get("posNo"));
                if (!StringUtils.isEmpty(matchedCondition.get("packageName"))) {
                    jsonObject.put("packageName", matchedCondition.get("packageName"));
                }
                jsonObject.put("assemblyType", matchedCondition.get("assemblyType"));
                i++;
            } else {
                result.remove(i);
            }
        }
    }

    private boolean matchesPattern(String str, String pattern, Map<String, Pattern> patternCache) {
        if (str == null || pattern == null) {
            return false;
        }
        // pattern 可能包含通配符，str 不含通配符
        Pattern p = patternCache.computeIfAbsent(pattern, k -> {
            String regex = "^" + k.replace("*", ".*") + "$";
            return Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        });
        return p.matcher(str).matches();
    }

    /**
     * 根据 keyValueMaps 过滤 JSONArray 中的元素，保留符合条件的记录
     *
     * @param result       需要过滤的 JSONArray
     * @param keyValueMaps 包含 key 和 value 的条件列表
     * @param version      模板版本信息，用于获取器件编码和器件封装的 key
     * @param templateType
     */
    private void filtByKeyValueMaps(JSONArray result, List<Map<String, String>> keyValueMaps, String version, String templateType) {
        // 如果 result 或 keyValueMaps 为空或 null，直接返回，不做处理
        if (result == null || result.isEmpty() || keyValueMaps == null || keyValueMaps.isEmpty()) {
            return;
        }

        // 将 keyValueMaps 按 key 分组，相同 key 的 value 放入 Set
        Map<String, String> keyToValuesMap = new HashMap<>();
        // 缓存正则表达式的 Pattern 对象，key 为原始 value（如 "mlf*-040-*"），value 为 Pattern
        Map<String, Pattern> patternCache = new HashMap<>();
        for (Map<String, String> condition : keyValueMaps) {
            String key = condition.get("key");
            String value = condition.get("value");
            // 外部已保证 key 和 value 非空，直接加入分组
            keyToValuesMap.put(key, value);
        }

        // 如果 keyToValuesMap 为空，直接返回
        if (keyToValuesMap.isEmpty()) {
            return;
        }

        // 获取器件编码和器件封装的 key
        String deviceCodeKey = null;
        String devicePackageKey = null;
        if (!Objects.isNull(version)) {
            deviceCodeKey = tbTemplateDao.getTbTemplateKeyByName(templateType, version, "器件编码");
            devicePackageKey = tbTemplateDao.getTbTemplateKeyByName(templateType, version, "器件封装");
            if (StringUtils.isEmpty(deviceCodeKey) || StringUtils.isEmpty(devicePackageKey)) {
                return;
            }
        }

        // 检查是否包含 deviceCodeKey 或 devicePackageKey
        boolean hasDeviceKeys = deviceCodeKey != null && devicePackageKey != null
                && (keyToValuesMap.containsKey(deviceCodeKey) || keyToValuesMap.containsKey(devicePackageKey));

        // 使用索引遍历并移除不满足条件的元素
        for (int i = 0; i < result.size(); ) {
            Object obj = result.get(i);
            if (!(obj instanceof JSONObject)) {
                // 如果不是 JSONObject，直接移除
                result.remove(i);
                continue;
            }

            JSONObject jsonObject = (JSONObject) obj;
            boolean allKeysMatch = true;

            // 如果同时包含 deviceCodeKey 和 devicePackageKey，特殊处理
            if ("deviceType".equals(templateType) && hasDeviceKeys) {
                String codeValue = jsonObject.getString(deviceCodeKey);
                String packageValue = jsonObject.getString(devicePackageKey);

                // 如果两者都为空，则不匹配
                if (StringUtils.isEmpty(codeValue) && StringUtils.isEmpty(packageValue)) {
                    result.remove(i);
                    continue;
                }

                // 至少有一个不为空，检查匹配
                boolean codeMatch = false;
                boolean packageMatch = false;
                // 检查 deviceCodeKey
                if (!StringUtils.isEmpty(codeValue)) {
                    String codeValueInCondition = keyToValuesMap.get(deviceCodeKey);
                    if(codeValueInCondition != null)
                    {
                        codeMatch = checkMatch(deviceCodeKey, codeValue, codeValueInCondition, patternCache, deviceCodeKey, devicePackageKey);
                    }
                }

                // 检查 devicePackageKey
                if (!StringUtils.isEmpty(packageValue)) {
                    String packageValueInCondition = keyToValuesMap.get(devicePackageKey);
                    if(packageValueInCondition != null){
                        packageMatch = checkMatch(devicePackageKey, packageValue, packageValueInCondition, patternCache, deviceCodeKey, devicePackageKey);

                    }
                }

                // 如果 deviceMatch 为 false，移除
                if (!(codeMatch && packageMatch)) {
                    result.remove(i);
                    continue;
                }

                // 移除 deviceCodeKey 和 devicePackageKey，剩余 key 按原有逻辑处理
                keyToValuesMap.remove(deviceCodeKey);
                keyToValuesMap.remove(devicePackageKey);
            }

            // 检查其他 key
            for (Map.Entry<String, String> entry : keyToValuesMap.entrySet()) {
                String key = entry.getKey();
                String matchValue = entry.getValue();

                // 获取 jsonObject 中该 key 的值
                String jsonValue = jsonObject.getString(key);

                // 如果 jsonValue 为空，则不匹配
                if (StringUtils.isEmpty(jsonValue)) {
                    allKeysMatch = false;
                    break;
                }

                boolean anyValueMatch = checkMatch(key, jsonValue, matchValue, patternCache, deviceCodeKey, devicePackageKey);

                // 如果该 key 的所有 value 都不匹配，标记为不满足
                if (!anyValueMatch) {
                    allKeysMatch = false;
                    break;
                }
            }

            // 如果不满足所有条件，移除当前元素
            if (!allKeysMatch) {
                result.remove(i);
            } else {
                i++;
            }
        }

        // 如果特殊处理过 deviceCodeKey 和 devicePackageKey，恢复 keyToValuesMap
        if ("deviceType".equals(templateType) && hasDeviceKeys) {
            for (Map<String, String> condition : keyValueMaps) {
                String key = condition.get("key");
                String value = condition.get("value");
                keyToValuesMap.put(key, value);
            }
        }
    }

    /**
     * 检查 jsonValue 是否与 searchValue 匹配
     * @param key 当前的 key
     * @param jsonValue jsonObject 中该 key 的值
     * @param searchValue 搜索条件的 value
     * @param patternCache 正则表达式缓存
     * @param deviceCodeKey 器件编码的 key
     * @param devicePackageKey 器件封装的 key
     * @return 是否匹配
     */
    private boolean checkMatch(String key, String jsonValue, String searchValue, Map<String, Pattern> patternCache,
                               String deviceCodeKey, String devicePackageKey) {
        // 处理 null 或空值情况
        if (jsonValue == null || searchValue == null) {
            return false;
        }

        // 特殊情况：对于器件编码和器件封装的反向模糊匹配
        if (key.equals(deviceCodeKey) || key.equals(devicePackageKey)) {
            // 情况 1：jsonValue 包含 *，反向模糊匹配
            if (jsonValue.contains("*")) {
                Pattern pattern = patternCache.computeIfAbsent(jsonValue, v -> {
                    String regex = v.replace("*", ".*");
                    try {
                        return Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
                    } catch (Exception e) {
                        return null;
                    }
                });

                if (pattern != null) {
                    Matcher matcher = pattern.matcher(searchValue);
                    return matcher.matches();
                }
            }
            // 情况 2：searchValue 包含 *，正向模糊匹配
            else if (searchValue.contains("*")) {
                Pattern pattern = patternCache.computeIfAbsent(searchValue, v -> {
                    String regex = v.replace("*", ".*");
                    try {
                        return Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
                    } catch (Exception e) {
                        return null;
                    }
                });

                if (pattern != null) {
                    Matcher matcher = pattern.matcher(jsonValue);
                    return matcher.matches();
                }
            }
            // 情况 3：都不含 *，精确匹配
            else {
                return jsonValue.equalsIgnoreCase(searchValue);
            }
        } else {
            // 其他 key 的匹配逻辑
            if (searchValue.contains("*")) {
                Pattern pattern = patternCache.computeIfAbsent(searchValue, v -> {
                    String regex = v.replace("*", ".*");
                    try {
                        return Pattern.compile(regex);
                    } catch (Exception e) {
                        return null;
                    }
                });

                if (pattern != null) {
                    Matcher matcher = pattern.matcher(jsonValue);
                    return matcher.matches();
                }
            } else {
                return jsonValue.equals(searchValue);
            }
        }

        return false; // 默认返回 false
    }

    private void filtByKeyValueMapsForMapObj(JSONArray result, List<Map<String, String>> keyValueMaps) {
        // 如果 result 或 keyValueMaps 为空或 null，直接返回，不做处理
        if (result == null || result.isEmpty() || keyValueMaps == null || keyValueMaps.isEmpty()) {
            return;
        }

        // 将 keyValueMaps 按 key 分组，相同 key 的 value 放入 Set
        Map<String, Set<String>> keyToValuesMap = new HashMap<>();
        for (Map<String, String> condition : keyValueMaps) {
            String key = condition.get("key");
            String value = condition.get("value");
            if (key != null && value != null) {
                keyToValuesMap.computeIfAbsent(key, k -> new HashSet<>()).add(value);
            }
        }

        // 使用正向遍历
        for (int i = 0; i < result.size(); ) {
            Object obj = result.get(i);
            if (obj instanceof LinkedHashMap) {
                LinkedHashMap<String, String> jsonObject = (LinkedHashMap<String, String>) obj;
                boolean allKeysMatch = true;

                // 检查每个不同的 key
                for (Map.Entry<String, Set<String>> entry : keyToValuesMap.entrySet()) {
                    String key = entry.getKey();
                    Set<String> values = entry.getValue();

                    // 如果 jsonObject 不包含该 key，跳过
                    if (!jsonObject.containsKey(key)) {
                        continue;
                    }

                    // 获取 jsonObject 中该 key 的值，并进行模糊匹配
                    String jsonValue = jsonObject.get(key);
                    boolean anyValueMatch = false;
                    if (jsonValue != null) { // 添加 null 检查
                        for (String value : values) {
                            if (!"".equals(jsonValue) && jsonValue.contains(value)) {
                                anyValueMatch = true;
                                break; // 只要有一个 value 匹配即可
                            }
                        }
                    }

                    // 如果该 key 的所有 value 都不匹配，标记为不满足
                    if (!anyValueMatch) {
                        allKeysMatch = false;
                        break;
                    }
                }

                // 如果不满足所有条件，移除当前元素
                if (!allKeysMatch) {
                    result.remove(i);
                } else {
                    i++; // 只有保留元素时才递增索引
                }
            } else {
                // 如果不是 LinkedHashMap，直接移除
                result.remove(i);
            }
        }
    }
    /**
     * 根据前端键值对列表条件迭代查询，找到最终重合的flowNo，并与已归档的flowNo集合做交集
     *
     * @param keyValueMaps    前端传入的键值对列表
     * @param archivedFlowList 已归档的flowNo列表
     * @return 符合所有条件且与已归档flowNo有交集的flowNo集合
     */
//    public Set<String> findCommonFlowNosOfDesignOrDeviceType(List<Map<String, String>> keyValueMaps, List<String> archivedFlowList) {
//        // 如果 keyValueMaps 为空或 null，返回空集
//        if (keyValueMaps == null || keyValueMaps.isEmpty()) {
//            return new HashSet<String>();
//        }
//
//        // 将 keyValueMaps 按 key 分组，相同 key 的 value 放入 Set
//        Map<String, Set<String>> keyToValuesMap = new HashMap<String, Set<String>>();
//        for (Map<String, String> condition : keyValueMaps) {
//            String key = condition.get("key");
//            String value = condition.get("value");
//            keyToValuesMap.computeIfAbsent(key, k -> new HashSet<String>()).add(value);
//        }
//
//        // 存储每个不同 key 的 flow_no 集合
//        Map<String, Set<String>> keyToFlowNosMap = new HashMap<String, Set<String>>();
//
//        // 迭代每个不同的 key，查询符合条件的 flow_no
//        for (Map.Entry<String, Set<String>> entry : keyToValuesMap.entrySet()) {
//            String key = entry.getKey();
//            Set<String> values = entry.getValue();
//            Set<String> flowNosForKey = new HashSet<String>();
//
//            // 对该 key 的每个 value 执行查询（OR 逻辑）
//            for (String value : values) {
//                // 使用 HashMap 替代 Map.of
//                Map<String, String> condition = new HashMap<String, String>();
//                condition.put("key", key);
//                condition.put("value", value);
//
//                List<TbTaskTemplateData> validData = tbTaskTemplateDataDao.getValidByCondition(condition);
//
//                // 如果查询结果为空，继续下一个 value
//                if (validData == null || validData.isEmpty()) {
//                    continue;
//                }
//
//                // 提取 flow_no 并加入集合
//                Set<String> currentFlowNos = validData.stream()
//                        .map(TbTaskTemplateData::getFlow_no)
//                        .filter(flowNo -> flowNo != null && !flowNo.isEmpty()) // 过滤空值
//                        .collect(Collectors.toSet());
//                flowNosForKey.addAll(currentFlowNos); // OR 逻辑：合并所有 value 的结果
//            }
//
//            // 如果该 key 没有任何 flow_no，返回空集
//            if (flowNosForKey.isEmpty()) {
//                return new HashSet<String>();
//            }
//
//            keyToFlowNosMap.put(key, flowNosForKey);
//        }
//
//        // 如果没有符合条件的 flow_no，返回空集
//        if (keyToFlowNosMap.isEmpty()) {
//            return new HashSet<String>();
//        }
//
//        // 计算所有不同 key 的 flow_no 交集（AND 逻辑）
//        Set<String> commonFlowNos = keyToFlowNosMap.values().stream()
//                .reduce((set1, set2) -> {
//                    set1.retainAll(set2); // 取交集
//                    return set1;
//                })
//                .orElse(new HashSet<String>());
//
//        // 与 archivedFlowList 取交集
//        List<String> archivedFlowSet = archivedFlowList != null ? archivedFlowList : Collections.<String>emptyList();
//        Set<String> finalFlowNos = commonFlowNos.stream()
//                .filter(archivedFlowSet::contains)
//                .collect(Collectors.toSet());
//
//        return finalFlowNos;
//    }

    /**
     * 根据 keyValueMaps 过滤数据库，返回符合条件的 knowledge_base_key 集合
     * @param keyValueMaps 包含 key 和 value 的条件列表
     * @param keyToMatchedIndexes 匹配的 keyValueMaps 索引
     * @return 符合条件的 knowledge_base_key 集合，使用 LinkedHashSet 保持顺序
     */
    @Transactional(transactionManager = "MysqlTransactionManager7")
    public LinkedHashSet<String> getFilteredByDataBase(
            List<Map<String, String>> keyValueMaps,
            Map<String, List<Integer>> keyToMatchedIndexes) {
        try{
            if (keyValueMaps == null || keyValueMaps.isEmpty()) {
                return new LinkedHashSet<>();
            }
            if (keyToMatchedIndexes == null) {
                throw new IllegalArgumentException("keyToMatchedIndexes cannot be null");
            }

            // 准备条件列表
            List<Map<String, String>> conditions = new ArrayList<>();
            for (Map<String, String> condition : keyValueMaps) {
                String key = condition.get("key");
                String value = condition.get("value");
                Map<String, String> newCondition = new HashMap<>();
                newCondition.put("key", key);
                newCondition.put("value", value);
                boolean containsStar = value != null && value.contains("*");
                if (containsStar) {
                    newCondition.put("value", value.trim().replaceAll("\\*+", "%"));
                }
                newCondition.put("containsStar", String.valueOf(containsStar));
                conditions.add(newCondition);
            }

            // 创建临时表
            String tempTableName = "temp_conditions_" + UUID.randomUUID().toString().replace("-", "");
            tbKnowledgeBaseDataDao.createTempTable(tempTableName);

            // 批量插入条件
            List<Map<String, Object>> tempData = new ArrayList<>();
            for (int i = 0; i < conditions.size(); i++) {
                Map<String, String> cond = conditions.get(i);
                Map<String, Object> row = new HashMap<>();
                row.put("idx", i);
                row.put("param_key", cond.get("key"));
                row.put("param_value", cond.get("value"));
                row.put("contains_star", Boolean.parseBoolean(cond.get("containsStar")));
                tempData.add(row);
            }
            tbKnowledgeBaseDataDao.insertIntoTempTable(tempTableName, tempData);

            // 查询
            List<Map<String, Object>> results = tbKnowledgeBaseDataDao.getValidByTempTable(tempTableName, conditions.size());

            // 清理临时表
            tbKnowledgeBaseDataDao.dropTempTable(tempTableName);

            // 处理结果
            LinkedHashSet<String> commonBaseKeys = new LinkedHashSet<>();
            for (Map<String, Object> result : results) {
                String baseKey = (String) result.get("knowledge_base_key");
                String matchedIndexesStr = (String) result.get("matched_indexes");
                List<Integer> matchedIndexes = matchedIndexesStr != null
                        ? Arrays.stream(matchedIndexesStr.split(","))
                        .map(Integer::parseInt)
                        .collect(Collectors.toList())
                        : new ArrayList<>();
                keyToMatchedIndexes.put(baseKey, matchedIndexes);
                commonBaseKeys.add(baseKey);
            }

            log.info("Matched keys: {}", commonBaseKeys);
            return commonBaseKeys;
        }catch (Exception e){
            log.error("getFilteredByDataBase error:", e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new LinkedHashSet<>();
        }

    }
//    private Set<String> findCommonFlowNosOfExcipientsData(List<Map<String, String>> keyValueMaps, List<String> archivedFlowList) {
//        // 如果 keyValueMaps 为空或 null，返回空集
//        if (keyValueMaps == null || keyValueMaps.isEmpty()) {
//            return new HashSet<String>();
//        }
//
//        // 将 keyValueMaps 按 key 分组，相同 key 的 value 放入 Set
//        Map<String, Set<String>> keyToValuesMap = new HashMap<String, Set<String>>();
//        for (Map<String, String> condition : keyValueMaps) {
//            String key = condition.get("key");
//            String value = condition.get("value");
//            keyToValuesMap.computeIfAbsent(key, k -> new HashSet<String>()).add(value);
//        }
//
//        // 存储每个不同 key 的 flow_no 集合
//        Map<String, Set<String>> keyToFlowNosMap = new HashMap<String, Set<String>>();
//
//        // 迭代每个不同的 key，查询符合条件的 flow_no
//        for (Map.Entry<String, Set<String>> entry : keyToValuesMap.entrySet()) {
//            String key = entry.getKey();
//            Set<String> values = entry.getValue();
//            Set<String> flowNosForKey = new HashSet<String>();
//
//            // 对该 key 的每个 value 执行查询（OR 逻辑）
//            for (String value : values) {
//                // 使用 HashMap 替代 Map.of
//                Map<String, String> condition = new HashMap<String, String>();
//                condition.put("key", key);
//                condition.put("value", value);
//
//                List<TbExcipientsData> validData = tbExcipientsDataDao.getValidByCondition(condition);
//
//                // 如果查询结果为空，继续下一个 value
//                if (validData == null || validData.isEmpty()) {
//                    continue;
//                }
//
//                // 提取 flow_no 并加入集合
//                Set<String> currentFlowNos = validData.stream()
//                        .map(TbExcipientsData::getFlowNo)
//                        .filter(flowNo -> flowNo != null && !flowNo.isEmpty()) // 过滤空值
//                        .collect(Collectors.toSet());
//                flowNosForKey.addAll(currentFlowNos); // OR 逻辑：合并所有 value 的结果
//            }
//
//            // 如果该 key 没有任何 flow_no，返回空集
//            if (flowNosForKey.isEmpty()) {
//                return new HashSet<String>();
//            }
//
//            keyToFlowNosMap.put(key, flowNosForKey);
//        }
//
//        // 如果没有符合条件的 flow_no，返回空集
//        if (keyToFlowNosMap.isEmpty()) {
//            return new HashSet<String>();
//        }
//
//        // 计算所有不同 key 的 flow_no 交集（AND 逻辑）
//        Set<String> commonFlowNos = keyToFlowNosMap.values().stream()
//                .reduce((set1, set2) -> {
//                    set1.retainAll(set2); // 取交集
//                    return set1;
//                })
//                .orElse(new HashSet<String>());
//
//        // 与 archivedFlowList 取交集
//        List<String> archivedFlowSet = archivedFlowList != null ? archivedFlowList : Collections.<String>emptyList();
//        Set<String> finalFlowNos = commonFlowNos.stream()
//                .filter(archivedFlowSet::contains)
//                .collect(Collectors.toSet());
//
//        return finalFlowNos;
//    }
    /**
     * 将任务模板数据添加到JSONArray中。
     *
     * @param paramKeys 期望的键列表，用于占位符
     * @param taskTemplateDataList 任务模板数据列表
     * @param special 是否按行特殊处理
     * @return 生成的JSONArray
     */
    private JSONArray addToTemplateDataArray(List<String> paramKeys,
                                             List<TbKnowledgeBaseData> taskTemplateDataList,
                                             boolean special) {
        // 检查输入数据是否有效
        if (taskTemplateDataList == null || taskTemplateDataList.isEmpty()) {
            return new JSONArray();
        }
        // 过滤掉row_sort为-1的模板数据
        taskTemplateDataList = taskTemplateDataList.stream()
                .filter(element -> !(new Integer(-1).equals(element.getRow_sort())))
                .collect(Collectors.toList());
        // 根据special参数选择不同的处理逻辑
        return special ? addSpecialTemplateData(paramKeys, taskTemplateDataList)
                : addCommonTemplateData(paramKeys, taskTemplateDataList);
    }

    /**
     * 处理special为true的情况，按行分组并生成JSONArray。
     */
    private JSONArray addSpecialTemplateData(List<String> paramKeys,
                                             List<TbKnowledgeBaseData> taskTemplateDataList) {
        JSONArray jsonArray = new JSONArray();

        // 使用 LinkedHashMap 保持行顺序,按照row_sort保证行对应关系
        Map<Integer, List<TbKnowledgeBaseData>> rowDataMap = taskTemplateDataList.stream()
                .collect(Collectors.groupingBy(TbKnowledgeBaseData::getRow_sort,
                        LinkedHashMap::new, Collectors.toList()));

        for (Map.Entry<Integer, List<TbKnowledgeBaseData>> e : rowDataMap.entrySet()) {
            JSONObject rowJson = new JSONObject();
            List<TbKnowledgeBaseData> rowData = e.getValue();
            log.info("[addSpecialTemplateData] row Data is null ? " + Objects.isNull(rowData));
            if (rowData != null) {
                for (TbKnowledgeBaseData data : rowData) {
                    if(Objects.isNull(data))
                    {
                        continue;
                    }
                    if(data.getParam_type() == 3 || data.getParam_type() == 4)
                    {
                        rowJson.put(data.getParam_key(), specialParamTypeProcess(data));
                    }else {
                        rowJson.put(data.getParam_key(), data.getParam_value());
                    }
                }
                // 确保所有 paramKeys 都有对应的值，即使为空
                for (String paramKey : paramKeys) {
                    rowJson.putIfAbsent(paramKey, null);
                }
                //rowJson.put("row_sort", rowData.get(0).getRow_sort());
//            }else{
//                // 补空行
//                for (String paramKey : paramKeys) {
//                    rowJson.putIfAbsent(paramKey, null);
//                }

            }
            jsonArray.add(rowJson);
        }

        return jsonArray;
    }

    /**
     * 处理special为false的情况，生成全局数据并复制到每一行。
     */
    private JSONArray addCommonTemplateData(List<String> paramKeys,
                                            List<TbKnowledgeBaseData> taskTemplateDataList) {
        // 创建全局数据模板
        JSONObject globalDataJson = new JSONObject();
        for (TbKnowledgeBaseData data : taskTemplateDataList) {
            if(Objects.equals(3, data.getParam_type()) || Objects.equals(3, data.getParam_type()))
            {
                globalDataJson.put(data.getParam_key(), specialParamTypeProcess(data));
            }else {
                globalDataJson.put(data.getParam_key(), data.getParam_value());
            }
        }

        // 为缺失的paramKeys添加空值
        for (String paramKey : paramKeys) {
            globalDataJson.putIfAbsent(paramKey, null);
        }

        // 为每一行复制全局数据
        JSONArray jsonArray = new JSONArray();
        jsonArray.add(globalDataJson);
//        for (int i = 0; i < maxRow; i++) {
//            // 使用 JSON 字符串进行深拷贝
//            JSONObject rowCopy = JSONObject.parseObject(JSONObject.toJSONString(globalDataJson));
//            jsonArray.add(rowCopy);
//        }
        return jsonArray;
    }
    private String updateVersion(String versionString) {
        // 正则表达式匹配 "YYYY_TNN" 格式的字符串
        String regex = "(\\d{4})_T(\\d+)";
        java.util.regex.Pattern pattern = java.util.regex.Pattern.compile(regex);
        java.util.regex.Matcher matcher = pattern.matcher(versionString);

        if (matcher.find()) {
            // 提取年份和版本号
            int year = Integer.parseInt(matcher.group(1));
            int versionNumber = Integer.parseInt(matcher.group(2));

            // 增加版本号
            versionNumber += 1;

            LocalDate now = LocalDate.now();
            year = now.getYear();

            // 格式化版本号为至少两位数（不足两位补零）
            String formattedVersionNumber = String.format("%02d", versionNumber);

            // 返回更新后的版本字符串
            return String.format("%d_T%s", year, formattedVersionNumber);
        } else {
            throw new IllegalArgumentException("Invalid version format: " + versionString);
        }
    }
    private String getPartPkgByNo(String partNo)
    {
        return "";
    }
    private int compareVersions(String v1, String v2) {
        Pattern pattern = Pattern.compile("(\\d{4})_T(\\d+)");
        Matcher matcher1 = pattern.matcher(v1);
        Matcher matcher2 = pattern.matcher(v2);

        if (!matcher1.matches() || !matcher2.matches()) {
            throw new IllegalArgumentException("版本号格式不正确");
        }

        int year1 = Integer.parseInt(matcher1.group(1));
        int year2 = Integer.parseInt(matcher2.group(1));

        if (year1 != year2) {
            return Integer.compare(year1, year2);
        }

        int number1 = Integer.parseInt(matcher1.group(2));
        int number2 = Integer.parseInt(matcher2.group(2));

        return Integer.compare(number1, number2);
    }

    @Override
    public Result getKnowledgeTableData(Set<String> baseKeySet) {
        try{
            return ResultUtil.success(tbKnowledgeBaseDataDao.getByBaseKeyList(baseKeySet));
        }catch(Exception e)
        {
            log.error("error: ", e);
            return ResultUtil.error(500, e.getMessage(), e.getMessage(), null);
        }
    }

    // 根据设计阶段筛选当前生产节点的数据
    private void filtByNodeName(JSONArray result, String type, String version,
                                String node, List<String> phaseKeyList) {
        if (StringUtils.isEmpty(node) || phaseKeyList.isEmpty()) {
            return;
        }

        // 处理多个节点名称，支持逗号分隔
        String[] nodeNames = node.split(",");
        Set<String> paramKeySet = new HashSet<>();
        for (String nodeName : nodeNames) {
            nodeName = nodeName.trim();
            if (!nodeName.isEmpty()) {
                String paramKey = tbTemplateDao.getTbTemplateKeyByName(type, version, nodeName);
                if (!StringUtils.isEmpty(paramKey)) {
                    paramKeySet.add(paramKey);
                }
            }
        }

        if (paramKeySet.isEmpty()) {
            return;
        }

        JSONArray filtered = new JSONArray();

        for (Object obj : result) {
            if (obj instanceof JSONObject) {
                JSONObject jsonObject = (JSONObject) obj;
                Object designPhaseValue = jsonObject.get(phaseKeyList.get(0));
                if (designPhaseValue instanceof String) {
                    String stringValue = (String) designPhaseValue;
                    if (stringValue.contains("[") && stringValue.contains("]")) {
                        try {
                            List<String> nodeValueList = JSON.parseArray(stringValue, String.class);
                            log.error("[filtByNodeName] node is " + node + " nodeValueList is " + nodeValueList);

                            for (String paramKey : paramKeySet) {
                                if (nodeValueList.contains(paramKey)) {
                                    filtered.add(jsonObject);
                                    break; // 已满足条件，跳出循环
                                }
                            }
                        } catch (JSONException e) {
                            log.error("[filtByNodeName] 解析失败: " + stringValue, e);
                        }
                    }
                }
            }
        }

        result.clear();
        result.addAll(filtered);
    }
    /**
     * 从 LinkedHashSet 中提取指定范围的子集，类似 List 的 subList 方法。
     *
     * @param set        输入的 LinkedHashSet
     * @param fromIndex  起始索引（包含），必须 >= 0
     * @param toIndex    结束索引（不包含），必须 <= set.size()
     * @param <T>        泛型参数
     * @return           新的 LinkedHashSet，包含指定范围的元素
     * @throws IllegalArgumentException 如果索引无效
     */
    private static <T> LinkedHashSet<T> subSet(LinkedHashSet<T> set, int fromIndex, int toIndex) {
        // 验证输入参数
        if (fromIndex < 0 || toIndex > set.size() || fromIndex > toIndex) {
            throw new IllegalArgumentException(
                    String.format("fromIndex=%d, toIndex=%d, size=%d", fromIndex, toIndex, set.size())
            );
        }

        // 创建新的 LinkedHashSet 以保持插入顺序和唯一性
        LinkedHashSet<T> subSet = new LinkedHashSet<>();

        // 使用迭代器遍历到指定范围
        Iterator<T> iterator = set.iterator();
        int currentIndex = 0;

        // 遍历并添加指定范围内的元素
        while (iterator.hasNext() && currentIndex < toIndex) {
            T element = iterator.next();
            if (currentIndex >= fromIndex) {
                subSet.add(element);
            }
            currentIndex++;
        }

        return subSet;
    }

    private List<String> parseParamValue(String jsonStr) {
        if (jsonStr == null || jsonStr.trim().isEmpty() || "[]".equals(jsonStr)) {
            return Collections.emptyList();
        }
        try {
            return JSON.parseArray(jsonStr, String.class);
        } catch (Exception e) {
            return Collections.emptyList();
        }
    }
    private List<TbKnowledgeBaseDataDTO> getValidByJsonArrayCondition(List<String> targetValues, String type, String designPahseKey) {
        // 参数校验
        if (type == null || type.trim().isEmpty()) {
            throw new IllegalArgumentException("模板类型（type）不能为空");
        }
        String tempTableName = "temp_" + UUID.randomUUID().toString().replace("-", "");
        try {

            tbTemplateDao.createTempTable(tempTableName);

            //在查询时就过滤 template_type = #{type}
            List<TbKnowledgeBaseDataDTO> validRecords = new ArrayList<>();
            List<TbKnowledgeBaseDataDTO> batch;

            int offset = 0;
            int limit = BATCH_SIZE;
            // 分批
            do {

                batch = tbKnowledgeBaseDataDao.getAllValidJsonRecordsByType(type, designPahseKey, offset, limit);
                validRecords.addAll(batch);
                offset += limit;
            } while (batch.size() == limit); // 如果不足一批，说明已到最后
            log.info("getAllValidJsonRecordsByType validRecords size is {}", validRecords.size());
            if (validRecords.isEmpty()) {
                return Collections.emptyList();
            }

            // 按 (version) 分组 keys（因为 type 已全局固定）
            Map<String, Set<String>> keysByVersion = new HashMap<>();
            for (TbKnowledgeBaseDataDTO record : validRecords) {
                String version = record.getVersion();
                List<String> keys = parseParamValue(record.getParam_value());

                keysByVersion
                        .computeIfAbsent(version, k -> new HashSet<>())
                        .addAll(keys);
            }

            // 构建缓存：version -> Map<param_key, param_name>
            Map<String, Map<String, String>> mappingCache = new HashMap<>();
            for (Map.Entry<String, Set<String>> entry : keysByVersion.entrySet()) {
                String version = entry.getKey();
                Set<String> keys = entry.getValue();

                List<TbTemplate> results = tbTemplateDao.findParamKeyNamesByKeys(version, type, new ArrayList<>(keys));
                log.info("findParamKeyNamesByKeys results size is " + results.size());
                Map<String, String> keyToName = results.stream()
                        .collect(Collectors.toMap(TbTemplate::getParam_key, TbTemplate::getParam_name));

                mappingCache.put(version, keyToName);
            }

            // 构建临时记录
            List<TempRecord> tempRecords = new ArrayList<>();
            for (TbKnowledgeBaseDataDTO record : validRecords) {
                String version = record.getVersion();
                List<String> keys = parseParamValue(record.getParam_value());

                Map<String, String> keyToName = mappingCache.getOrDefault(version, Collections.emptyMap());

                List<String> realNames = keys.stream()
                        .map(keyToName::get)
                        .filter(Objects::nonNull)
                        .collect(Collectors.toList());

                if (realNames.isEmpty()) continue;

                TempRecord temp = new TempRecord();
                temp.setKnowledge_base_key(record.getKnowledge_base_key());
                temp.setFlow_no(record.getFlow_no());
                temp.setReal_param_value(JSON.toJSONString(realNames));
                tempRecords.add(temp);
            }

            if (!tempRecords.isEmpty()) {
                tbTemplateDao.batchInsertTempTable(tempRecords, tempTableName);
            }

            return tbTemplateDao.queryFromTempTable(targetValues, tempTableName);

        } finally {
            tbTemplateDao.dropTempTable(tempTableName);
        }
    }

}
